Unit IV Project
For this assignment, you will continue to work with the scenario you began in Chapters 1-3. The initial scenarios are discussed on pages 17-18 of your textbook and continued on pages 37-41 and pages 60-61. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)
Instructions:
1. Reread the introduction to your selected scenario on page 17 or 18 and the continuations on pages 37-41 and pages 60-61.
2. Read your scenario for Chapter 4 on pages 79-81.
3. Review the “Deliverables” section below.
4. Review the “Suggestion for Scenarios” section on page 81 of your textbook for important details and ideas to assist you with your deliverables
5. Review Appendix D, Common Relational Patterns, on page 191 of your textbook.
6. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, and page 80. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
7. When you have completed all of the deliverables, compress the files together into a single .zip file for upload.
Deliverables:
1. Complete the numbered tasks for your scenario. The table below shows where you can find each list:
2. Your scenario “To Do” list may ask you to review your work with another student or group. That step is not required for this assignment.
Scenario Page Number for “To Do” Grandfield College Page 80
Unit V Project
For this assignment, you will continue to work with the scenario you began in Chapters 1-4. The initial scenarios are discussed on pages 17-18 of your textbook and continued on pages 37-41, pages 60-61, and pages 79-80. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)
Instructions:
1. Reread the introduction to your selected scenario on page 17 or 18 and the continuations on pages 37-41, pages 60-61, and pages 79-80.
2. Read your scenario for Chapter 5 on pages 100-101.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 101 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, and page 101. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. When you have completed all of the deliverables, compress the files together into a single .zip file for upload.
Deliverables:
1. Complete the numbered tasks in the “To Do” section for your scenario. The table below shows where you can find each list:

Scenario Page Number for “To Do” List Grandfield College Page 101

Unit VI Project

For this assignment, you will continue to work with the scenario you began in Chapters 1-5. Reread the information from each of these assignments at the end of each chapter. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)

Instructions:
1. Reread your selected scenario at the end of all previous chapters.
2. Read your scenario for Chapter 6 on pages 123-124.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 124 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, page 101, and page 124. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. Your final deliverable will be a script file that you create from your database. To generate an SQL Script, right click on the database, go to TASKS on the context menu, and then GENERATE SCRIPTS. In the wizard you should change “Script Data” from “false” to “true” and then generate a script of the database you have built to turn in.

Deliverables:

1. Complete the numbered tasks in the “To Do” section for your scenario, EXCEPT number 6. You DO NOT need to create a data dictionary. The table below shows where you can find each list of tasks:
Scenario Page Number for “To Do” List Grandfield College Page 124 Unit VII Project For this assignment, you will continue to work with the scenario you began in Chapters 1-6. Reread
Unit VII Project

For this assignment, you will continue to work with the scenario you began in Chapters 1-6. Reread the information from each of these assignments at the end of each chapter. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)

Review the Textbook/Course Materials section of the syllabus and appendix B of your textbook for guidance on using the correct software programs.

Instructions:
1. Reread the introduction to your selected scenario at the end of all previous chapters.
2. Read your scenario for Chapter 7 on page 155.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 156 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, page 101, page 124, and pages 155-156. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. Your SQL queries should be created, tested, and then copied and pasted into a Word file to be submitted to your instructor. Contact your instructor if you have any questions.

Deliverables:

1. Review the scenario and complete the task for your given scenario. The table below shows where you can find each list of tasks: Grandfield College Page 155

Unit VIII Project
For this assignment, you will continue to work with the scenario you began in Chapters 1-7. Reread the information from each of these assignments at the end of each chapter. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)
Instructions:
1. Reread your selected scenario at the end of all previous chapters.
2. Read your scenario for Chapter 7 on page 175 to 177.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 177 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, page 101, page 124, pages 155-156, and page 176. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. When you have completed all of the deliverables, compress the files together into a single .zip file for upload. Contact your instructor if you have any questions.
Deliverables:
1. Complete the numbered tasks in the “To Do” section for your scenario. The table below shows where you can find each list of tasks: Grandfield College Page 176

Chapter 1 Who Needs a Database 17
Things to Look Up
1. Look up Codd’s 12 rules. Choose one of the rules to explain
to your fellow students.
2. Look up the history of SQL. How many revisions of the 5.
standard have there been? What was added in the most
recent one?
3. Use the Internet to look up database-related jobs. Make a
brief report summarizing what you find.
4. A recent trend for major commercial database developers is
to offer free “Express” versions of their databases. Microsoft
has SQL Express, Oracle has Oracle Express, and DB2 has 6.
DB2 Express. Visit the company Web sites and look up these
Express editions. What features does each one have? What
limits do they have? How do they compare to each other?
For some time there have been attempts to move beyond
relational databases, to find some new data model. One
direction has been to move toward object-oriented databases.
Another area of research is into XML-based databases.
Choose one of these to look up and write a brief summary of
what the model entails and what is the current status of the
model.
Look up statements of work. What are some additional elements
that can be included?
Practices
1. Think about keeping a home budget. Would it be better
to keep the budget in spreadsheets or to create a budget
database? Write a couple of paragraphs that describe your
choice and at least three reasons to justify it.
2. Think of a small business or nonprofit that you know that
could use a database. Explain why you think a database
would help the business. List the benefits the business or
nonprofit would gain from a database.
3. An entity is something the database is concerned with. For
instance, a movie rental business would probably have an
entity called DVD. Attributes are things that describe the
entity. Make a list of possible attributes for a DVD entity.
4. You are going to interview a small business owner about
creating a database for his sandwich shop and bakery. Make
a list of questions for this initial interview. Remember at this
point you just want the big picture and major requirements.
Don’t get too deep into the details.
5. Think about the sandwich shop and bakery in Question 4.
List what you think the major topics would be.
6. A dentist office wants a database to track its appointments.
The specifics of what they want to track are as follows:
a. All customers of the dental office
b. Customer appointments
c. Which dentist serves each customer at the appointment
d. Which assistants assist each dentist
e. In brief, what services were provided at the appointment
f. The database will not track bills and payments (they
have a separate software for this purpose)
Write a statement of scope for the dental office database.
Scenarios
These scenarios are designed to give you the opportunity to
experience database development from beginning to end. Each
has its own unique challenges. The scenarios can be pursued
individually or in small groups. I would suggest choosing one
scenario that interests you to follow throughout the term. Later,
if you are so inclined you can return and work through some of
the others.
7. List the major themes for the dentist office database in
Practice 6.
8. How long do you think it would take to gather the information
needed to make the dentist office database in Practice 6.
_Discuss_what steps_you think would be involved and how
long it might take to build the database.
9. Look around the school or think of some businesses or nonprofits
with whom you are familiar. Identify at least one
situation in which a database could be of help.
a. Describe why a database would improve the situation.
b. Describe what the major topics of this database would
be.
c. Write a statement of work for this database.
10. An instructor has been keeping all his grade books in Excel
for years. He has a separate spreadsheet for every course. In
the spreadsheet he tracks the scores for every assignment
and test and then assigns term grades based on the overall
averages. Whenever a former student contacts him requesting
a letter of recommendation or whenever the administration
requests information concerning a student in a previous
term he has to open and search several spreadsheets to get
the student’s information.
a. What are some of the advantages a database would
have over the current system for this instructor?
b. What would be some of the major topics for the
database?
c. Write a statement of work for the preceding database.
WILD WOOD APARTMENTS
Wild Wood Apartments owns 20 different apartment complexes
in Washington, Oregon, California, and Idaho. Each
apartment complex contains anywhere from 10 to 60 separate
apartments, of varying sizes. All apartments are leased with a
6 month or yearlong lease.
18 Chapter 1 Who Needs a Database
It is the company’s practice to hire one of the tenants to
manage each apartment complex. As manager, he or she needs
to admit new tenants to the building, collect rents from existing
tenants, and close out leases. The manager also needs to maintain
the apartments by executing any repairs, replacements, or
renovations. These can be billed back to the parent company.
For acting as manager, the tenant gets free rent and a stipend.
The stipend varies depending on the size of the apartment
building.
Each manager is expected to send a report to the Wild
Wood Apartments company headquarters in San Francisco
every quarter. This report summarizes the occupancy rate, the
total revenues in rent, the total expenses in maintenance and
repairs, and so on. Currently, managers fill out a paper form
and mail it back to headquarters. Many apartment managers
have complained that preparing this report is a very difficult
and time-consuming process. Also, the managers at corporate
headquarters have expressed concerns about the accuracy and
_verifiability of the reports.
To allay these concerns and to improve the ease and efficiency
with which the apartment managers conduct their daily
business, the company is proposing to develop a centralized
database that can be used by the managers to track the daily
business of their apartment building and to prepare their
reports
To do
1. List the major topics for this database.
2. Write a draft statement of work. Include a brief history,
a statement of scope, objectives, and a preliminary
timeline.
3. Documentation: Start a notebook, either electronically
or physically, to record your progress with the scenario
database. Add the statement of work and any notes to
the notebook.
VINCE’S VINYL
Vince Roberts runs a vintage record shop in the University district.
His shop sells 45’s, LPs, and even old 76 RPM records.
Most of his stock is used—he buys used vinyl from customers
or finds them at yard sales and discount stores—but he does sell
new albums that are released on vinyl. For a couple of years, he
has kept most of his inventory either in his head or in a spiral
notebook he keeps behind the sale counter. But his inventory
and his business have grown to where-that is far from sufficient.
Vince is looking for someone to make him a database. He
knows he needs to get a better handle on several aspects of
his business: He needs to know the extent and condition of his
inventory. He needs to know the relative value of his inventory—some
records are worth a fortune; some are nearly worthless.
He also needs to track where, from whom, and for how
much he purchased his stock. He needs to track his sales. He
often is not entirely sure how much money he has spent or how
much money he has earned.
In addition he would like to allow customers to make specific
requests and notify them if a requested item comes in. More
generally he would like to make an email list of interested customers
in order to let them know about new items of interest.
Someday, he would like to expand his business online. But
he knows he needs to have everything under control before then.
To do
1. List the major topics for this database.
2. Write a draft statement of work. Include a brief history,
a statement of scope, objectives, and a preliminary
timeline.
3. Documentation: Start a notebook, either electronically
or physically, to record your progress with the scenario
database. Add the statement of work and any notes to
the notebook.
GRANDFIELD COLLEGE
The law requires that any business, including a school, track
its software. It is important to know what software the school
owns, in what versions, and what the license agreement for that
software is. There are several different licensing schemes. The
least restrictive is a “site” license that allows an institution to
have a copy of the software on any machine on the business
property. Other licenses specify a certain number of active copies
for an institution but don’t worry about which machine or
user has the copy. The more restrictive licenses do specify one
copy per specific machine or user.
Whatever the license agreement for particular software, it is
essential for the institution to know which software is installed
on which machine, where_thatmachine_isiocated,ancLwhich—
users have access to that machine. It is also important to track
when the software is uninstalled from a machine, and when a
machine is retired.
An additional useful feature of any software-tracking
database would be to track software requests from users to
determine (1) if a copy of the software is available and (2) if
it is something that should be purchased. All installations are
reviewed and must be approved.
For now, the school just wants the database to track faculty
and staff computers and software. Software for student
machines is a separate and complex issue and will be treated as
a separate project at a later time.
To do
1. List the major topics for this database.
2. Write a draft statement of work. Include a brief history,
a statement of scope, objectives, and a preliminary
timeline.
3. Documentation: Start a notebook, either electronically
or physically, to record your progress with the scenario
database. Add the statement of work and any notes to
the notebook.
INESTLAKE RESEARCH HOSPITAL
A hospital is conducting a double blind test of a new depression
drug. It will involve about 20 doctors and about 400 patients.
Half of the patients will get the new drug and half will get traditional
Prozac. Neither the doctors nor the patients will know
who is getting which drug. Only two test supervisors will know
who is getting what. The test will last about 18 months. Each
doctor will see 20 patients initially, though it is expected some
patients will drop out over time. Each patient will be coming
in twice a month for a checkup and interviews with their doctor.
The drugs will be dispersed in a generic bottle by the two
supervisors one of whom is a pharmacist.
payment amounts into a spreadsheet. While he was working,
a tenant came in and handed him a check. Joe thanked the tenant
and added the check to the pile. When he had finished, he
checked his list against a list of tenants. He told me that three
had not paid their rent yet.
He called each of the three. The first did not answer, so he
left a message. “I am not too worried about him,” Joe told me.
“He isn’t always on time, but he always pays within the 5-day
grace period.”
I asked about the grace period. Joe answered, “The company
allows a renter to be up to 5 days late without a penalty. If you
pay after that there is a $100.00 penalty tacked on to the rent.”
He called the second renter. She was at home and asked if
he could wait until the 10th. Joe said OK and then explained to
me; “She’s an older woman and dependent on Social Security
and retirement checks. I give her a little more leeway. The company
lets me because she has lived here forever and has always
been a good tenant. This last one though is just no good.” He
picked up the phone and called. He got no answer, and there
was no answering machine. Joe told me that he was about ready
to evict this last tenant. He is habitually late, and he is actually
2 months behind in his rent. Joe tells me how difficult it is to
actually evict someone.
While he is telling me stories about past evictions, the phone
rang. A woman in apartment 211 told him that her stove wasn’t
working. Joe opened a second spreadsheet and entered some
Chapter 2 0 Gathering Information 39
of the details. He also wrote some notes on a pad of paper. He
reassured the woman that he would deal with it quickly and
promised to come by in the afternoon.
After 4 hours, I thanked Joe for his time and left him to his
lunch.
To do
1. Make a list of questions that you would ask about these
forms and reports.
2. Identify the stakeholders for Wild Wood Apartments.
3. Create a plan for an hour-long interview with representatives
of these stakeholders. Then meet with the instructor
to discuss possible answers to the questions.
4. Create a questionnaire of at least five questions for the
managers of the 20 apartment buildings.
5. Look at the Job Shadow Report. Do you see any exceptions
to the general rules? Do you see any new business
rules uncovered? What additional questions arise from
the report?
VINCE’S VINYL
Vince hasn’t kept very complex records, but he does have a
few things he can show you. The first thing he has is an example
of the notes he takes when he purchases an album from a
customer.
Date 5/14/2013
Seller’s Name Seller’s Phone Number Album Notes Condition Paid ($)
John Raymond 206.555.2352 Rubber Soul Amer. Not British vers. 2nd edition,
good Sleeve
fair 4
Marilyn Taylor 206.555.0945 Led Zepplin IV Not orig. Sleeve damaged, vinyl good good 4.75
Jennifer Louis 206.555.4545 Gift of the flower to
the Gardner
Rare Donovan, box set, box condition
poor, but vinyl excellent
excellent 12.25
Laura Hall 206.555.2080 Dark Side of the Moon good 4.45
Here is an example of a sale to a customer:
Date
Customer
5/12/2013
Album Price ($) Tax ($) Total ($)
John Larson Dylan, Blond on Blond 19.95 1.65 21.60
Tabitha Snyder America 5.95
Joni Mitchell, Blue 6.25
Joan Baez, Ballads 4.20 1.36 17.76
Brad Johnson McCartney, Venus
and Mars
5.00 0.42 5.42
Maureen Carlson Decembrists,
The Crane wife
15.50
Muddy Waters 7.75 1.92975 25.18
Job Shadow Report
I sat with Vince for a full day of work. The morning was quiet,
and Vince spent the time sorting through a stack of albums that
he had purchased earlier in the week. He took each one out of
the sleeve and inspected it carefully. “Sometimes I catch things
that I didn’t see when I actually purchased it,” he explained
to me. “It is too late now, of course, to do anything about it,
but I want to be fair to the people I sell it to.” He put a sticker
on the cover and put “good” and a price of $6.50. I asked him
about how he classified and priced things. He told me he had
four levels: mint, good, fair, and poor. Mint was only for things
that were nearly perfect. Good meant there were no scratches
and the vinyl was not warped and not too worn. Fair meant
the vinyl was a bit more worn and might have a light scratch or
two. Poor meant the vinyl was scratched and probably warped.
He didn’t buy poor vinyl unless it was an extremely rare album.
Prices were based on what he thought the album would bring.
He based it mostly on experience.
After a while, a customer came in. He asked if Vince had
seen a copy of an old album. He commented that he didn’t think
it had ever made the transition to CD. Vince said he had seen it,
but he didn’t have a copy currently, but if the customer wanted
he would take his name and number and let him know when
he next got a copy. The customer agreed and then, after looking
around for about 20 minutes, returned to the counter with five
albums. Vince wrote down each album title and the price and
then added the prices on a hand calculator. The total came to
$35.50. Vince said, “Make it thirty, and we’ll call it good.” Vince
40 Chapter 2 • Gathering Information
explained that it was good for business. It made the customer
feel good, and they were more likely to come back. Several
more customers came in, and their transactions followed a similar
pattern.
In the afternoon, a customer came in with a stack of albums
he wanted to sell to Vince. Vince went through the albums, taking
each one out of its sleeve and inspecting it. In the end, he
split the albums into two piles. He told the customer he was
interested in the first pile of about 12 albums and would *offer
him $20.00 for them. The customer pulled one album out of the
pile Vince had selected and said “I thought this one might be
worth a little more. It is a first print.” Vince looked at it again.
“Yes it is, but it is scratched and only in fair condition. Still, I’ll
make it $25 if that makes it seem more fair to you.” The customer
agreed. Vince told him he wasn’t really interested in the
second pile of albums. The customer could either take them
back or Vince would put them on his 5-for-a-dollar pile. The
customer chose to leave them.
Vince put the albums in a pile by his desk. Several more
customers came and went. Vince chatted pleasantly with all of
them. Several purchased an album or two. At about four, Vince
turned the open sign in his window to closed, and I thanked
him for his time and left.
1. Study Vince’s sample notebook entries. Make a list of
questions you would ask about the data in them.
2. Identify the stakeholders in Vince’s record store.
3. Prepare an interview with Vince and two of his best
customers: one who both sells albums to Vince and
buys, and one who mostly just buys. Then meet with
the instructor to discuss possible answers to the
questions.
4. Create a questionnaire for those who sell albums to Vince
about changes they would like to see in the process.
5. Look at the Job Shadow Report for Vince. Do you see arty
exceptions? What additional business rules do you see?
What additional questions does the report raise?
GRANFIELD COLLEGE
The software management team has several spreadsheets to
keep track of software. They show you several samples. The
first is just a listing of software:
Software Version Company License Type
Windows Vista Business, Service Microsoft MS Site
Pack 2
MS Office 2007 Microsoft MS Site
Visual Studio Professional 2008 Microsoft MS Instructional
PhotoShop CSS3 Adobe Adobe1
Filelilla 5 Filelilla Open Source
German 2.5 LanguageSoft LanguageSoft1
The second is a key to the different licensing agreements
and types:
To do
License Type Start Date End Date Terms Pricing Pricing Unit
MS Site 7/1/2009 7/1/2013 Can install as many copies as needed on campus and on
laptops controlled by the school. Includes all service patches,
updates, and version changes
12500 5 yrs
Ms Instructional 7/1/2010 7/1/2015 Used for instructional purposes only. Cannot be used for school
development projects
3000 5 yrs
Adobe1 7/1/2009 7/1/2015 Reduced price per installed copy, maximum of 25 active copies 450 Per active copy
Open Source 7/1/2009 7/1/2020 Free for use as long as registered 0
LanguageSoft1 7/1/2012 7/1/2016 25 copies 5200 For 25 copies
Here is an example of the list of who has what software:
CCS Number Location Assigned User
3214
Software
Rm214
Install date
Cardwell
Rmv Date
Vista Business 5/3/2013
Ms Office 5/3/2013
PhotoShop 6/4/2013
DreamWeaver 6/4/2013
CCS Number Location Assigned User
3114 Rm212 Larson
Software Install Date Rmv Date
Vista Business
MsOff ice
Visual Studio Pro
DreamWeaver
4/15/2013
4/15/2013
6/12/2013
6/14/2013 7/12/2013
Chapter 2 0 Gathering Information 41
And, finally here is sample of a request for new software:
Requests
CCS Number User Request Date Software Reason Response Res Date Status
2123 Johnson 5/20/2013 Camtasia lam conducting several online classes.
I need to be able to create visual
demos to post to the class Web site
We don’t currently have 5/24/2013 Pending
a license for Camtasia but
will explore acquiring one
Job Shadow Report
I spent the day on 4/12/2013 following Sheri, a member of
the software management team at Grandfield College. The
first thing she did after settling into her office was check a
spreadsheet that listed pending installations. She showed me
the list and told me that she had about six installations to do
that morning. She also noted that it was the most boring part
of her job. “Nothing like watching the progress bar on the
monitor for hours at a time,” she said. Next, she checked her
emails. There were three requests for additional software. She
opened a spreadsheet and entered the request information.
She told me that she would check later to see if the school
had the software or if it was something they would have to
purchase-Af-it-was-a purchase,=she would-have-to-get-pernus=
sion. She replied to each of the emails to acknowledge their
request.
After noting the requests, she looked again at the installation
to be done. She went to a cupboard and pulled out some
disks. She told me that some software can be installed from
a network drive, but for some she has to bring the media.
She also grabbed a notebook. We went to the first office. She
spoke for a few moments with the woman who occupied the
office. They laughed at a few things. Sheri said that with luck
the installations should take no more than 30 minutes. The
woman left the office to let Sheri work. Sheri logged into the
computer as administrator and slipped in a DVD. She started
the install.
I asked her about the notebook. Sheri told me that she carried
it for two reasons. If there were any problems with the
install that she couldn’t solve, she would write down the error
messages and take them to the other techs to resolve. She also
would note in the book whether the installation was a success
or not. She didn’t put it in the spreadsheet until the installation
was complete and successful.
The rest of the morning, Sheri moved from office to office
installing software. On that day, at least, there were no major
installation issues. While we waited, she told me about other
days that didn’t go so easily. She told me about how difficult
it could be to troubleshoot a bad install, and how obscure and
undocumented settings could require hours of research before
they were discovered and resolved.
The installations were finished by lunch. After lunch, Sheri
checked with the department receiving new software and
packages. There were several that had arrived. Sheri carefully
unpackaged each arrival and noted it in a spreadsheet. Then
she checked the licensing agreements. Some she knew, others
she had to check, often looking up the licensing agreement
online. “Everybody is different,” she told me. “Some let you
install the software anywhere on-site. Some will only allow
a certain number of copies. Some can be placed on a server,
while some only allow client installations. Some are tied to a
particular user. It would make my life easier if things were
consistent.”
Late in the afternoon, Sheri received a call for an instructor
requesting disks for a piece of software. She told him
“sure,” if he would come up and get it. He arrived at the door
shortly afterward. She gave him the disks and made him sign
for them in a notebook. “I’ll have them back to you tomorrow
morning,” he said. Sheri explained, “There are two or
three instructors who have administrative privileges on their
machines. They do their own installations and their own
suppprt.” I asked if they track the software on those instruc- _ _
tors’ machines. Sheri told me that they do as best as they
can, but the instructors can do pretty much as they want. To
get the admin privileges, they have to sign a release saying
they won’t violate any licensing agreements and that they
accept the fact that the school IT staff will not support their
computers.
Following this, it was time to quit. Sheri shut down her computer.
I thanked her for allowing me to follow her and wished
her “good evening.”
To Do
1. Study the samples given earlier. Make a list of questions
you would ask about the data in them.
2. Identify the stakeholders in the software-tracking
system.
3. Prepare a plan for a 1-hour interview with representatives
of the stakeholders listed earlier. Then meet with the
instructor to discuss possible answers to the questions.
4. Create a questionnaire for faculty and staff about changes
they would like to see in the request process.
5. Review the job shadowing report. Do you see any exceptions?
Do you see any additional business rules? What
additional questions does the report raise?
WESTLAKE RESEARCH HOSPITAL
The drug study is unique in many ways. For one, the forms and
the type of information they capture are more complex. For
another, privacy rules make it difficult to shadow doctors or
researchers. But, still, if you are going to create a database, you
must begin to gather the requirements and figure out what data
are needed to be tracked.
Here is the Initial Medical Form that each patient is asked
to fill out:
42 Chapter 2 . Gathering Information
Initial Medical History Form
Name Date
Birth Date
Address
City State Zip
Phone Email
List any prescription or nonprescription medicines you are currently taking.
List any known allergies to medicines.
Have you ever been told you had one of the following?
Lung disorder: 0 yes 0 no
High blood pressure: 0 yes 0 no
Heart trouble: 0 yes 0 no
Nervous disorder: 0 yes 0 no
Disease or disorder of the digestive tract: 0 yes 0 no
Any form of-cancer 0-yes- 0-noDisease
of the kidney: 0 yes 0 no
Diabetes: 0 yes 0 no
Arthritis: 0 yes 0 no
Hepatitis: 0 yes 0 no
Malaria: 0 yes 0 no
If you answered yes to any of the above, please explain:
Disease or disorder of the blood? (describe)
Any physical defect or deformity? (describe)
Any vision or hearing disorders? (describe)
Any life-threatening conditions? (describe)
How would you describe your depression?
a. Mild and continuous
b. Mild but intermediate
c. Moderate and continuous
d. Moderate but intermittent
e. Severe and continuous
f. Severe but intermittent
When were you first diagnosed with depression?
Which of the following symptoms have you experienced?
O Sleep difficulties
O Loss of appetite
O Loss of libido
O Inability to leave house
O Anxiety in social situations
O Thoughts of suicide
(continued)
Chapter 3 • Requirements and Business Rules 59
Although it is not listed, students have student IDs, which could be used to uniquely
identify each student. Most tutors are also students and would have a student ID, but
not all tutors are students. Perhaps there is some sort of employee ID. She will have to
ask Terry. Each course has a unique name so that could be a potential key for that entity.
For the session, the session date or the session time, perhaps in combination,
could be a key, but that wouldn’t really be unique because different tutors could have
sessions on the same day at the same time. If the tutor ID was added to the key, that
could be unique.
After all the analysis, Sharon feels ready to get to work on the logical design of the
database.
DOCUMENTATION
Requirements and business rules are an essential part of the documentation of any
database. A developer needs to refer to them many times during the development
process. She needs to constantly check to see if the database is meeting all the requirements.
Is anything being left out? Are there elements that weren’t in the initial requirements?
And, if there are, should they be added to the requirement list, or should they be
removed? When the database is completed, the requirements and business rules guide
how the database is tested. The developer should look at each requirement and rule and
make sure the database satisfies it. Terry, for instance, requires the database to be able
to produce reports with unduplicated counts of students. The database must be tested
to make sure this is possible.Tf itJsnt, thenitmustbe_adjusted
Additionally, anytime someone needs to change the database, make an addition,
or replace it all together, that person will need to review the original requirements to
see if the changes add to them or alter them in some way. If no one documented the
original requirements and rules, the person will have to recreate them by talking to the
users of the database. He or she will, in short, have to do the whole process of gathering
requirements over again. If the database has been in use for a long time, this can be difficult.
Often, many of the people involved in the original development of the database
have retired or left for other jobs. It is possible that no one will remember exactly the
reasons for creating the original database.
The requirements and business rules for a database should be clearly marked and
stored with the other database documentation.
The initial attempts listing nouns and dividing them into attributes do not necessarily
need to go into the formal database documentation, but it is a good idea to keep
them and other project notes in a folder or notebook of some kind. It is always useful to
be able to review your notes and revisit why you made the decisions you did. You may
find that you need to modify the original ideas based on later evidence.
Things We Have Done
In this chapter, we have
• revisited the problem domain by reviewing the issues with
the current system developed a list of requirements for
each user
• reviewed the business rules for the tutoring database
• reviewed the materials collected in the previous chapters
and extracted nouns that may become entities and attributes
Things to Look Up
1. Look up “Requirement Analysis” on the Web. What kinds
of topic headings do you find?
2. Look up two or more definitions for “Business Rules.”
• organized the nouns into preliminary entities and
attributes
• looked for attributes that could serve as candidate keys, that
is, attributes that could potentially work as primary keys for
the entities
3. Look up an article on the Web that discusses natural versus
surrogate keys in databases. Which does the author prefer?
4. What are some additional plusses or minuses of each?
60 Chapter 3 e Requirements and Business Rules
Vocabulary
Match the definitions to the vocabulary words:
1. User access
2. Server
3. Surrogate key
4. Stakeholder
5. Requirement
6. Natural key
7. Client
8. Trigger
9. Composite key
10. Business rule
11. Problem domain
Practices
a. A program that requests a service
b. A key that consists of more than one attribute
c. A program in SQL that is triggered by a database event
d. A program that offers a service to requesting programs
e. A key based on one or more “natural” attributes of an entity
f. A rule about how data are acquired, stored, or processed
g. The general problem area with which a database is concerned
h. An artificial key, often just an incremented number
i. Something a database must do to meet a business need
j. A person or program that makes some use of the database
k. The permissions a user has to take to view database objects and data
Use the following scenario for each of the practice exercises:
You have been asked to build a database for a pet foster
and adoption shelter. The agency is a nonprofit that takes in
stray or abandoned pets and places them with foster caregivers-until-the-pet-is-
adopted-Foster-caregivers- are volunteers;
though they must first be screened. The database needs to
track all animals in its care, their species, breed, name, and
condition. It also needs to track all approved foster caregivers
and the animals currently in their care. Foster caregivers
are also supposed to turn in monthly reports on the animals
in their care. The database also needs to track the adoptions of
the animals.
Currently, volunteers come into the shelter and fill out a
paper form. After a background check, they are added to a file.
Some volunteers complain that they are never contacted again.
The shelter staff admits, they tend to go with foster caregivers
they know, and some people get forgotten in the file. The
shelter has also occasionally lost track of an animal in foster
care when the caregiver failed to turn in the monthly reports.
Another recurring problem is that when someone comes into
the shelter looking to adopt, it is not always easy or even
possible to let them know about all the animals available for
adoption.
Scenarios
Ideally, the shelter would like people to be able to register
as a volunteer online. They would like to be able to call up a
list of all available foster volunteers. They would also like to be
able to pull up all the animals of the kind a potential adopter
is-interested-in and-know-exactly-where those-animals-are-and
who is caring for them.
1. Make a list of some of the major issues with the current
system used in the shelter.
2. Identity who the major stakeholders, are and list them.
3. Would animals be stakeholders in this ‘database? Explain s„
why or why not.
4. Make a list of requirements for each of the stakehotciers
showing how he or she would interact with the databak:
5. Make a list of business rules for the shelter.
6. What might be some of the shelter database security issues?
7. Make a list of all the nouns in the description of the
shelter.
8. Take the list from practice5, and determine whlat you
think would be the major entities.
9. List the attributes for each of the entities you listed in
practice6.
10. Identify some candidate keys.
WILD WOOD APARTMENTS
The Wild Wood Management team is ready to see some results.
You have a meeting with them at the end of the week. It is time
to analyze and organize all the information. Look back at the
material from earlier chapters.
To do
1. Make a list of issues with the current system.
2. Make a list of the database requirements for each stakeholder
involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms, and make a list of all the
nouns in them. Do the same for the interview, the questionnaire,
and the Job Shadow Report. Then set up some
preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
VINCE’S VINYLS
You are eager to show Vince some progress on the database.
You sit down to analyze all the materials you have gathered to
see if you can make some sense of them. Make sure you review
the material in the previous two chapters.
To do
1. Make a list of issues with the current system.
2. Make a list that shows the database requirements for
each stakeholder involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms and make a list of all the
nouns in them. Do the same for the interview, the questionnaire,
and the Job Shadow Report. Then set up some
preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
GRANDFIELD COLLEGE
It is imperative that the college get the software tracking database
online as soon as possible. You have assured the management
team that you will be able to show some progress
very soon. It is time to set down and review all the forms and
materials.
To do
1. Make a list of issues with the current system.
2. Make-a list of the database requirements for each stakeholder
involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms and make a list of all the
nouns in them. Do the same for the interview, the questionnaire,
and the Job Shadow Report. Then set up some
preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
WESTLAKE RESEARCH HOSPITAL
The drug study is set to begin in just a few months’ time. It is
important to make some progress toward the database. It is
time to gather all the materials you have collected and try to
make some sense of them.
To Dos
1. Make a list of issues with the current system.
2. Make a list of the database requirements for each stakeholder
involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms and make a list of all
the nouns in them. Do the same for the interview, the
Chapter 3 o Requirements and Business Rules 61
questionnaire, and the Job Shadow Report. Then set up
some preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
SHOW TIMES: LOCAL SHOWS AND ACTS
You want to get the database to track bands and venues in place
as soon as possible. There are a lot of interesting shows scheduled
in the not too distant future. You set down to review the
materials you have collected so far. Look back at the previous
chapters:
To do
1. Make a list of the issues with the current way of getting
the word out about shows.
2. Make a list that shows the database requirements for
each stakeholder who will be involved with the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the documents and make a list of
all the nouns in them. Do the same for the interview, the
questionnaire, and the Job Shadow Report. Then set up
some preliminary entities and attributes. _
6. Identify some candidate keys.
7. Documentation: Store the list of database requirements
and business rules in your database notebook.
SUGGESTIONS FOR SCENARIOS
Review all the documents and interviews from Chapter 2. It
will probably be necessary to talk with your instructor or other
students to answer some of the questions about the scenarios
that have not yet been answered. A certain amount of invention
is expected here.
Look at the requirements in terms of each user or actor. It is
much easier to do it this way rather than trying to just make a
general list of requirements. The actor gives you a clearer focus
on what the database needs to do in a particular instance. The
same holds true of the database security. It is much easier to
understand in terms of each actor’s access needs.
When making the initial list of nouns, don’t try to distinguish
between entities and attributes. Just list them in the order
you encounter them. Save the analysis until you are done.
When you do the analysis, remember entities are major
themes or elements. They will tend to stand out. If you find a lot
of words clustered around a single topic, the topic is likely the
entity and the words clustered around it are probably attributes.
It is good to remember that this is a very preliminary stage
of analysis. There aren’t any absolutely right or wrong answers
at this stage.
Databace Design
ENTITY RELATION DIAGRAMS
Having organized her materials and determined the business rules, in this chapter, Sharon begins the logical
design of the tutoring database. Using Microsoft Visio, she defines the database entities, their attributes, and
tre reletiOnships a-m-o-n-g—thenT. 4
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
Use the database modeling template in Microsoft Visio
Create entities and add attributes
Determine the appropriate relationship between entities
3 Resolve many-to-many relationships with a linking table
ESIGNING THE DATABASE
Sharon is ready to prepare the logical design of the database. The logical design, she knows, is separate from any
consideration of which DBMS the database is going to be developed on. It doesn’t take into account how the files
will be stored or accessed. It ignores any features or limitations of the target DBMS. It is focused purely on the
logical structure of the entities and their relationships with each other.
THINGS TO THINK ABOUT
The logical design of a database is the same no matter what
the RDBMS is going to be.
Physical design is specifically tailored to the features and
limits of a particular RDBMS.
What is the advantage of separating the logical from the
physical design?
LOGICAL DESIGN
The entity relation design without
regard to what RDBMS or system it
will be on.
PHYSICAL DESIGN
The design adapted to the RDBMS
and system constraints and features.
62
For this process, she is going to use crow’s foot database notation template in
Microsoft Visio and create a new Entity Relation Diagram or ERD. (For a complete
description of opening the entity diagram in Visio, see Appendix C). There are other
templates available, each with its own advantages and disadvantages, but this is the
one she learned and it makes sense to stick with it.
Chapter 4 Database Design 79
FIGURE 4-39 Recipe and
Ingredient Entities
Recipe Ingredient
ra Recipe Key • IngredientKey
5. Create a diagram that shows how you would resOlve the
relationship in Practice 4.
6. An instructor has decided that he needs a relational database
to store grades in. He has defined the following three
entities: Student, Course, and Assignment. What kind of
relationship exists between these entities? 9.
7. Create an ERD for the instructor’s database. Don’t worry about
the attributes, but give each entity a primary key attribute.
Remember to watch out for many-to-many relationships.
8. A dentist office has three dentists, two hygienists, five den- 10.
tal assistants, and two administrative assistants to maintain
the office paper work. They are creating a database to track
Scenarios
appointments and also to track who works with each patient.
So far the database developer has defined the following entities:
Employee (which includes all categories of employee
including the dentists), Customer, and Appointment. Which
entities have many-to-many relationships?
Create an ERD that shows the relationships among the entities
in the dentist office in Practice 8. Remember several
employees (a dentist, an assistant, a hygienist, and so on.)
can be involved in a single appointment for a customer.
Look at the diagram for Practice 8. Identify which entities
are domain entities, which are linking entities, which are
lookup, and which, if any, are weak entities.
INILD=WOOD=APARTMENTS
The managers at Wild Wood Apartments are anxious to see
some progress on their database. They have answered your
questions and now want to see some results. They really want
the new database to be in place before the beginning of the new
fiscal year in July. It is time to design the database.
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign
keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the Wildwood
Apartments business model represented by domain
entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)? Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tabls used for attnbutes that-have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
VINCE’S VINYL
Vince is convinced he is losing money on several of his transactions.
He is anxious to get the new database in place to help
him get control over his business. He has been polite, but keeps
checking on your progress. It is time to show some results.
Create a logical design of Vince’s database. Use the following
steps:
To do
1. Review all the requirements and business rules that you
have gathered from your interviews and after reviewing
Vince’s records.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the Vince’s business
model represented by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
80 Chapter 4 Database Design
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)?Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
GRANDFIELD COLLEGE
A team from the Software Alliance could show up any day.
The IT services manager is eager to get the tracking database
in place. It is time to show some progress. Create the logical
design of the database following these steps:
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all thentities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the software tracking
system represented by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)? Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
WESTLAKE RESEARCH HOSPITAL
It is imperative that the database be ready before the actual
clinical trials begin. The staff at Westlake is anxious to see some
results. It is time you show them the logical design of their database.
Follow these steps:
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign
keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the clinical trial represented
by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)?Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
. Are-lookup-tables-used for-attributes-that-have-a-set- –
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
SHOW TIME LOCAL VENUES AND BANDS
The artists and venues you have talked to are really anxious to
see how far you have gotten on the database. It is time to get
down to business and design the database.
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign
keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the elements necessary to track shows and
artists represented by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)? Is
the appropriate entity is defined as the one side of a
Normalization and
Design Review
Sharon takes her entity relation diagram to her database professor, Bill Collins. Together they review it for
completeness and conformity to the first three normal forms. Then Sharon takes the design to Terry for a final
discussion and review before beginning the physical design of the database.
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
O Evaluate an entity against the first three normal forms
Ei Remove all repeating lists or arrays (First Normal Form)
O Remove functional dependencies (Second Normal Form)
O Remove all transitive dependencies (Third Normal Form)
O Understand the importance of design review
THE DESIGN REVIEW
Sharon knocks on Professor Collins’s door early in the morning. He greets her and offers a chair. He has the
diagram printed out with a few hand-written notes and arrows. He shows her the diagram and begins to explain
his notes. “This is a pretty good diagram. You have all the basic elements in place.”
“I owe it all to what I learned in your class.”
“Thanks.” He looks at the diagram, “I think we should begin by looking at the entities and making sure
they are all properly normalized. Then we should check to make sure all the relationships are correct, and
finally, we can discuss whether the diagram completely captures everything needed to meet the business
requirements.”
“Sound’s good. Let’s start.”
“OK, let’s start with normalization. First, we will see if it conforms to the First Normal Form.”
Things You Should Know
Normalization
Normalization is the process of removing anomalies and redundancies from database design. There are three specific kinds of
anomalies that can occur in database design:
NORMALIZATION
• Insertion anomalies
• Update anomalies
The process of removing anomalies
and redundancies from database
• Deletion anomalies design.
82
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
SUGGESTION FOR THE SCENARIOS
These scenario exercises are probably the most difficult in the
book. The first suggestion is to not panic. Creating ERDs is an
iterative process. No one expects you to have a perfect diagram
Chapter 4 Database Design 81
on the first attempt. The trick is to add entities one at a time.
Don’t try to imagine the whole diagram all at once. Look at each
entity separately. Does it have the appropriate attributes? Is the
primary key defined? After the main entities are on the diagram,
look at the relationships between two entities at a time. What
kind of relationship do they have? Do you need a linking table?
Also check for other such issues. Remember, also, that some
entities have no direct relationship between them. Don’t fall into
the trap of trying to relate every entity to every other entity.
Discussion helps. Others can see issues and approaches that
you might have missed. It is always good to have another pair
of eyes looking over your work.
Chapter 5 o Normalization and Design Review 99
which tutors were currently tutoring versus which ones were no longer tutoring. It is
possible that you could get the same information by querying the Session tables. If a
tutor doesn’t have any current or future dates listed, will he or she be inactive?”
“That wouldn’t always be true. A tutor could be active but not have scheduled
anything for the next two weeks. I think the status field is better. What is the TutorKey?”
Sharon smiles, “I am not entirely sure. A student has a student ID and that can be
the StudentKey. Most tutors are students but not all of them. Do you create an identifying
number?”
“Yes. We give all tutors an employment ID. If they are a student, it is the same as
their student ID; if not, we give them one that looks just like a student ID.”
“Good. That makes that easy.”
They review each of the remaining entities. Terry has questions for each, but after
the full review, she is satisfied that it captures all the information that she will need.
Sharon thanks her. “Now I am ready to actually build the database. We will need
to get together again to decide what we want to build it in.”
Terry looks down at her calendar. “How soon do you want to meet?”
“How about Monday? I think I know what we should use, but I would like to do
a little research.”
“OK, how does 9:00 AM work?”
“That should be fine.”
DOCUMENTATION
It is useful to keep multiple versions of the entity diagram, noting changes made to DENORMALIZATTON
conform to normal forms. Again, these can be useful to later developers who need to Joining tables that were separated
make changes to your original design. One change that is often made on high-volume in the normalization process to
transaction databases is to apply a process called “denormalization.” In denormaliza- improve performance.
tion, some entities that were separated in the normalization process are rejoined. This
is done for processing and query speed. It is not a process that should be done lightly.
Every act of denormalization reopens the possibility of the various anomalies. But
sometimes the sheer size and volume of transactions on a database make it necessary to
denormalize if the users are not to experience delays.
A database should always be fully normalized first and denormalized only as necessary
for performance. Both the fully normalized design and the changes made for
denormalization should be fully documented.
Things We Have Done
In this chapter, we have
• looked at three types of database anomalies: insert, update,
and delete
• introduced normal forms
Vocabulary
Match the vocabulary word with its definition
• reviewed database designs for First Normal Form
• reviewed database designs for Second Normal Form
• reviewed database designs for Third Normal Form
• reviewed database designs for completeness
1. Normal forms
2. Update anomalies
3. Deletion anomalies
4. First Normal Form
5. Denormalization
6. Insertion anomalies
— a. Where deleting some data inadvertently also removes other data
— b. Removes transient dependencies
— c. Where the same data must be updated in several places, creating the possibility of
mismatched or inaccurate data
— d. Attributes that are related to each other, rather than the key. They form subthemes within
the entity
— e. Rules for removing anomalies and redundancies
— f. An attribute that depends on another attribute, not the key, for its meaning
100 Chapter 5 • Normalization and Design Review
7. Second Normal Form
8. Transient dependencies
9. Functional dependencies
10. Third Normal Form
– g. Removes functional dependencies
– h. The inability to insert data because some other unknown data is required
– i. Removes repeating groups and arrays
– j. The process of rejoining tables that were separated during the normalization process to
improve performance
Things to Look Up
1. Look up database anomalies. See if you can find a good
example explaining each kind of anomaly.
2. Look up the definition of functional dependency. Can you
find a good example?
3. Look up the definition of transitive dependency. Can you
find a good example?
Practices
4. Look up one of the normal forms we did not cover. See if
you can explain it to someone in the class.
5. Look up “denormalization,” and why anyone would want
to do it.
Charlie has a large book collection. He was keeping track of it
in a spreadsheet, but it has grown big enough that he wants
to convert it into a real database. Here is a sample from the
spreadsheet:
1.
2.
3.
What are some of the potential problems with this layout if
carried directly to the database?
Which of the columns in the example are multivalued?
Create a table that would show how you would convert the
sample data into First Normal Form. (Hint: Break the information
in the Titlescolumn into separate fields. Books are
Author Author Country Titles
James Taylor England JavaScript Essentials, South Tech
Books, London, 2010, $14; HTML5
Exposed, Webby Books, London,
2012, $15.50
May Norton United States Big Data Big Promise, Data Press,
San Francisco 2012, $25
Jessica Lewis United States Database Development for the
Cloud, Data Press San Francisco,
$20.35; Data Services, Future Tech
Press, New York $12.95
Scenarios
WILD WOOD APARTMENTS
It is almost time to actually begin building the apartment database,
but you must make sure that the design is solid and that it
captures all the data required by Wild Wood Apartments. The
first step is a design review; then you must review the diagram
for completeness.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
rules and needs of Wild Wood Apartments?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
separated by semicolons)
4. Create an entity diagram for the table you made in Practice 3.
5. List all the functional dependencies you find in the sample
data.
6. Identify and list some potential candidate keys for the new
entities.
7. Create an entity diagram that shows the structure of the
data in Second Normal Form.
8. List any transitive dependencies you find.
9. Create an entity diagram that shows the database in Third
Normal Form.
10. Describe the process you went through to achieve the
normal forms.
VINCE’S VINYL
You have told Vince that you can begin building the database
very soon now, maybe even next week. But before you do that,
you need to make sure the design is solid and complete.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
rules and needs of Vince’s Vinyl?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
GRANDFIELD COLLEGE
You have promised to begin building the database within the
next couple of days. But before you do that, you have to review
the design for normalization and completeness.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness.
Do the entities capture all the data needed to meet
the business rules and needs of Grandfield College IT
Department?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
WESTLAKE RESEARCH HOSPITAL
The start of the double-bind test is approaching rapidly. There
is a great deal of pressure on you to begin building the actual
database. Before you can -do That, though, you must perform
a final review to make sure the database is normalized and
complete.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
rules and needs of Westlake?
Chapter 5 0 Normalization and Design Review 101
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
SHOW TIMES: LOCAL SHOWS AND ACTS
You are almost ready to begin building the database, but before
getting down to actually creating the database, you must review
the diagram for normalization and completeness.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
Shows and Venues?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
SUGGESTIONS FOR SCENARIOS
Normalization is difficult. The trick is to take each normal form
one at a time. Look at each entity one at a time, to see if each
conforms to the First Nonnal Form. Make sure there are no
repeating groups or multivalued attributes. If there are, then
break them out into new entities. Then repeat the process for
the Second Normal Form. Look at each entity, and make sure
that each is about only one thing. Again, if you find an entity
that is about more than one thing, break it into new entities.
Finally, repeat the process for Third Normal Form, looking for
transitive dependencies, attributes that depend on an attribute
that is not the key, for their value.
As with the design process itself, the normalization process
benefits from discussion and multiple inputs. It is crucial to
have others review the results.
Physical Design
Now that she has the logical design completed, Sharon works on the physical design of the database. The
first thing to decide is what database management system to use. After considering several, Sharon decides
on SQL Server Express. She creates a new database with a data file and a log file. She creates the tables in
the new database, selecting the appropriate data type and setting any constraints for each column. She also
sets up the relationships among the tables. Finally, when she has set up all the database objects, she enters
5 or 10 rows of sample data so she can test the database.
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
• Compare database management systems and determine which best suits current needs
ud Implement a physical design of the database based on the logical ERDs
la Choose appropriate data types for columns
VIEnter sample data into tables
CHOOSING THE MANAGEMENT SYSTEM
Sharon finally feels comfortable with her design. Now it is time to begin actually creating the database. The
first question she must resolve is which database management system to use. One of the first criteria is that
it shouldn’t cost the school anything. That still leaves open several options. Oracle Express and DB2 Express
are tempting because she would love to explore them. But the fact that she doesn’t know either also means
a longer learning curve. Additionally, she knows that the IT staff is unfamiliar with them. The same holds
true, though to a lesser extent for MySQL and PostGres SQL. Both are free and actually more powerful than
any of the express editions, but she is less familiar with them. The IT staff has some familiarity with MySQL,
but still Sharon doesn’t think she can afford the learning curve at this time. That leaves Access and SQL
Server Express.
Things You Should Know
Choosing a DBMS
Choosing the appropriate DBMS requires a great deal of analysis. There are several important factors to consider.
• Compatibility with your network and operating systems
• Hardware and software requirements for the DBMS
• Features of the DBMS in relation to your database requirements
• Familiarity and expertise in the DBMS for database developers and IT personnel
102
Chapter 6 Physical Design 123
Customers
2065552123 Lamont NULL 161 South Western Ave NULL NULL 98001
2065553252 Johnston Apt. 304 1215 Terrace Avenue Seattle WA 98001
2065552963 Lewis NULL 520 East Lake Way NULL NULL 98002
2065553213 Anderson Apt 10 222 SouthernStreet NULL NULL 98001
2065552217 Wong NULL 2832 Washington Ave Seattle WA 98002
2065556623 Jimenez Apt 13 B 1200 Norton Way NULL NULL 98003
Employee Table
cmanning Manning Carol 3/12/2012
btaylor Taylor Bob 4/16/2010
skristoph Kristopherson Stephen 6/2/2014
Product Table
262
CustomerOrder Table
1000 10/8/2014 14:15:00 2065552963 cmanning
1001 10/8/2014 14:25:00 2065556623 cmanning
1002 10/8/2014 14:30:00 2065552963 cmanning
1003 10/8/2014 15:15:00 2065552123 skristoph
1004 10/10/2014 11:15:00 2065552217 btaylor
1005 10/10/2014 12:02:00 2065556623 btaylor
Order Detail Table
1 1000 soda 2 7.25
2 1000 brdstks 1 2.50
3 1000 specialM 1 7.35
4 1001 specialL 1 15.00
5 1002 soda 2 7.25
6 1002 basicM 3 20.00
7 1003 basicM 1 7.35
8 1003 top 4 4.00
9 1004 basicL 1 13.50
10 1005 basicM 2 14.70
Scenarios
WILD WOOD APARTMENTS
You have completed the designs for the apartment management
database. You reviewed it and all the business rules with the owners,
and they are eager to proceed. Now you need to take your
design and translate it into an actual database. Once you have
done that, you know that you will need to enter data to test the
database, to make sure it does, in fact, store all the required data.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
124 Chapter 6 0 Physical Design
4. Create a database diagram, and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types
for each column.
VINCE’S VINYL
Vince is eager to get going. Just today, he had a customer come
in and sell him a dozen old albums. One is quite rare and could
be worth a lot of money. Vince doesn’t want to lose track of it.
He is ready to get organized and start entering his transactions
in the database. You review your design with him and promise
that you will begin building the database immediately. But, you
remind him, it is important to test the database before actually
starting to use it for the business.
To Do
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types for
each column.
GRANDFIELD COLLEGE
The management is afraid of a software audit. The chief systems
manager just came from a meeting where he heard that
a school had just been fined $25,000 for illegally installed software.
The current tracking system probably couldn’t hold up to
an audit. It is crucial that this new database be up and running
soon. You assure the management that it will be done as soon as
is possible, but you want to make sure that it really does what it
is supposed to do. If you implement before it is ready, it might
make matters worse rather than better.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types
for each column.
WESTLAKE RESEARCH HOSPITAL
The Drug study is falling into place. Several potential participants
have already been interviewed. It is vital that the database
be in place soon. You assure the management that you are ready
to begin actually making the database objects but that it is essential
you test and evaluate it before they start to commit data to it.
You promise that you will deliver it as soon as possible.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types for
each column.
SHOW TIMES: LOCAL SHOWS AND ACTS
You have completed your design and checked it out. Now it is
time to actually create the database. Once you have built it, you
can add sample data to it so you can test whether it meets all the
requirements and fulfills all the business rules.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types
for each column.
SUGGESTIONS FOR SCENARIOS
Make sure your primary keys and foreign keys have the same
data type and same precision (length). Follow your diagram
and make one table at a time. In the database diagram, always
drag the relationship from the primary key to the foreign key.
Before confirming the relationship, always make sure that the
table and column names are correct in the dialog box.
If you need to adjust a table in the database diagram, you
can right click on it and under View, select Normal. That will
display the column names, data type, and whether it will accept
nulls. You can edit the table in this view.
When entering data, you must enter data in the primary key
tables before you can enter into the child or foreign key tables.
The foreign key must match the primary key exactly.
Now that Sharon has built the database and entered some data, she sets out to test the design and make
sure she can satisfy the business requirements. To do this, she is going to use SQL and SQLExpress’s query
analyzer.
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
ElName the main events in the development of SQL
Run SELECT queries with a variety of criteria
Use the Aggregate functions COUNT, AVG, SUM, MIN, and MAX
1/Use date, time, and other built-in functions
o Join two or more tables in a query
i3INSERT, UPDATE and DELETE records
o Use SQL to test business rules
RUNNING QUERIES
It has been a long day. Sharon had two classes of her own today and then she tutored three students in beginning
database. But she feels some pressure to finish with the tutoring database. Before she can give it to Terry,
she needs to test it to make sure it can do all the things that are required of it. She has entered the sample data;
now she is going to run some sample queries. SQL
She takes out her laptop and sits at the kitchen table. She starts the SQL The programming language used Server Management Studio and opens up the databases in the Object window. to manipulate data and data objects
She clicks the NEW QUERY button on the toolbar to get a New Query Window. in a relational database.
Tu t °
File Edit View Project Debug Tools Window Help
Li I New Quey
FIGURE 7-1 New Query
To get started, in the query window she types
SELECT * FROM Tut
125
126 Chapter 7 SQL
She clicks Tutor in the list and then clicks the Execute button and gets these results:
TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHire Date TutorStatus
980010000 Roberts Martha 2065551467 mroberts@yahoo.com 2012-01-06 Active
980010001 Brown Susan 2065553528 Sb4@hotrnaiLcom 2013-02-01 Active
980010002 Foster Daniel 2065553490 Foster32@aol.com 2014-02-12 Active
980010003 Anderson Nathan 3065556320 NULL 2014-03-02 Inactive
980010004 Lewis Ginger 2065552985 ginger@hotmail.com 2014-03-15 Active
FIGURE 7-2 Result Set
Things-You Should Know
SQL is the programming language used for manipulating database objects and data in relational databases.
It is both an ANSI (American National Standards Institute) and an ISO (International Standards
Organization) standard.
The first version of SQL was developed at IBM in the 1970s to work with their RBase relational
database. The first ANSI standard for SQL was issued in 1986. The ISO committee ratified the standard
in 1987. This first standard was not widely used. Database technologies had already moved past
it. Most database manufacturers had already added features that were not included in the standard.
A major revision was issued in 1992. This standard was much more robust and is still the de facto
-standard-of-many RDBMSs-today; More changes were added to-the standard-in-1999 to define-the – –
use of triggers and procedures. Revisions in 2003 and 2006 defined how to incorporate XML and
XQuery into SQL.
Most RDBMSs comply with the standard to a fairly high degree. What this means for the user
is that the SQL they write for one product will translate fairly easily to another product. Much of
the SQL you write for SQL Server, for instance, will work without change in Oracle or MySQL. Each
RDBMS, however, is free to add proprietary features on to SQL as well as to implement the standard.
Typically, these features are additional functions or administrative extensions.
The Nature of SQL
SQL is a declarative language. This means it is different from the procedural languages you may have
encountered in other programming languages such as C++ or Java or C# or Visual Basic. In those
languages you have to specify how something is to be done. You have to carefully list each step in
the proper order to accomplish a task. In SQL, you say what you want done, not how to do it. In the
preceding example, for instance, Sharon writes
DECLARATIVE LANGUAGE SELECT * FROM Tutor
A language in which programmers
declare what they want to do not
how they want to do it.
The SELECT tells the DBMS you want to retrieve data. The * is a wildcard that says “select all
columns.” The FROM keyword directs the RDBMS to a table in the current database. The statement
as a whole declares “return all the columns and all the rows from the table Tutor.” Again, it declares
what you want to do, not how to do it. The RDBMS determines how to process the request. Different
RDBMSs will process it differently because they have developed different query optimization engines
in order to produce the results as efficiently and quickly as possible.
PROCEDURAL LANGUAGE
A language in which a programmer
defines how to do a given procedure.
THINGS TO THINK ABOUT
What are the advantages of a declarative Ian- What advantages might a procedural language
guage as opposed to a procedural language? have over a declarative language?
SQL is not case sensitive, though the column names and values can be if the database options
are set to be case sensitive. It is traditional, however, to type SQL keywords in all uppercase for readability.
SQL also ignores most white spaces. That means you can organize an SQL statement on the
page any way that makes it most readable to you. In many DBMSs, SQL statements are terminated
by a semicolon. SQL Server does not require the semicolon, though using one can be a good habit to
develop. In this book, the semicolons are not included.
Chapter 7 • SQL 155
Vocabulary
Match the term with the definition.
1. Aggregate Function
2. Alias
3. Cross Join
4. DDL
5. Declarative Language
6. DML
7. Equi Joins – g.
8. Procedural Language
9. Qualified Name
10. Scalar Function
11. SQL
12. Transact SQL
Practices
Data Manipulation Language
A function that operates on a single row at a time
A substitute name for a column or table
Programming language that defines how to accomplish a task
A join that uses the WHERE clause and the equal sign to specify relationships
The language of RDBMS
Data Development Language
A function that operates on multiple rows at a time
A database name that shows a hierarchy of ownership with dot notation
Microsoft SQL Server’s brand of SQL
A programming language in which a programmer defines what to do, not how to do
A join in which each row of the first table is joined with every row in a second table
Use the Pizza database created in the last chapter’s practices
and write SQL to answer these questions:
1. List all last names, phone numbers, and zip of the customers.
2. List only-those from-Zip-code98002.
3. List all the customers that have no first address entered in
the database.
4. List all the products that are priced higher than $10.
5. List all the products priced between $5 and $7.
6. List all the customers whose last name starts with L.
7. What is the average price of a product?
Scenarios
WILD WOOD APARTMENTS
Now that the basic database is in place, the Wild Wood
Apartments managers are eager to see the database in action
and see if it meets all their needs and requirements. It is time to
look at the business rules and test them with some SQL. Look
at the business rules you developed previously, and design
some SQL queries to test them. Documentation: Set up a test
plan. List the rule, the SQL you wrote, and the results. Also
note whether the database passes or fails the test. Your queries
should include the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATEs and/or a DELETE.
VINCE’S VINYL
It is time to test Vince’s database to see if it truly meets his
needs. It is time to look back at the business rules and test
them with some SQL. Look at the business rules you developed
previously, and design some SQL queries to test them.
Documentation: Set up a test plan. List the rule, the SQL you
wrote, and the results. Also note whether the database passes
or fails the test. Your queries should include the following:
8. What is the highest price of a product?
9. What is the total due for order 1003?
10. join the product and the OrderDetail table so that the result
contains the product name, product unit size, and product
urut price as well the charged price. Do it for order 1000.
List all the order and order details for each order made by
the customer with the phone number 2065556623.
Change the price of breadsticks to 3.00.
Process a pizza order for a new customer (this will involve 3
INSERT statements).
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATES and/or a DELETE.
GRANDFIELD COLLEGE
The college is feeling pressurized to get the new system in place.
There could be an inspection of their IT services any time now,
and they want to be ready. It is time to look at the business rules
and test them with some SQL. Documentation: Set up a test
plan. Look at the business rules you developed previously, and
design some SQL queries to test them. List the rule, the SQL you
wrote, and the results. Also note whether the database passes or
fails the test. Your queries should include the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATES and/or a DELETE.
WESTLAKE RESEARCH HOSPITAL
The research program is almost ready to begin. Westlake is in
the process of interviewing potential patients and doctors. It is
11.
12.
13.
156 Chapter 7 • SQL
important that the database be ready soon. It is also important
that it does what it is supposed to do. It is time to look at the business
rules and test them with some SQL. Look at the business
rules you developed previously, and design some SQL queries
to test them. Documentation: Set up a test plan. List the rule, the
SQL you wrote, and the results. Also note whether the database
passes or fails the test. Your queries should include the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATES and/or a DELETE.
SHOW TIMES: LOCAL SHOWS AND ACTS
Everyone is eager to see this database up and running. Look
at the requirements and business rules you established in the
design process. You will use SQL to test those rules and make
sure the database functions the way it needs.
Documentation: Set up a test plan. Look at the business rules
you compiled in Chapter 3 and design some SQL queries to test
them. List the rule, the SQL, and the result. Also note whether
the database passes or fails the test. Your queries should include
the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATEs and/or a DELETE.
SUGGESTION FOR SCENARIOS
Review your business rules. Many are probably simple to test,
requiring only SELECT statements. Others may be harder. Try
the simple ones first.
You may also find that you need to adjust your sample data.
It may be necessary to insert some data that show a violation of
a rule, or you may need to insert data in order to compare different
dates or times.
Most SQL mistakes are syntax errors. Missing commas or
extra commas are common suspects. The error messages in
the query analyzer do not always pinpoint the exact error. If
you double click the error message, it will place your cursor in
the vicinity of the error. Look all around the region. A missing
comma or a misspelled word may be causing an error later in
the code.
Another common error with joins is the ambiguous column.
This usually involves a key column that occurs in other
tables as a foreign key. Since it occurs in more than one table,
SQL Server cannot determine which table it is from. These columns
should always be qualified with the all name or table
alias.
Chapter 8 • Is It Secure? 175
Vocabulary
Match the definitions to the vocabulary words:
1. Authentication
2. Authorization
3. Disaster recovery plan
4. Big Data
5. Permission
6. Policies
7. Procedures
8. Roles
9. Schema
10. Stored procedures
11. Views
Practices
a. an action that a user has been granted the right to do in a database
b. A stored query or filter that reflects a user’s view of the data
c. The process of confirming a user is who he or she claims to be
d. A set of related permissions
e. A rule for how to do some activity
f. One or more SQL statements grouped to be executed together
g. Enormous structured and unstructured datasets
h. The processes of assigning permissions to authenticated users
i. A plan to recover data and maintain availability after any kind of disaster
j. Step-by-step plan for accomplishing a task
1. Review the pizza database we built in Chapter 6 and queried
in Chapter 7. Identify the users of the database, and determine
what kind of access to the tables each of them needs.
2. Develop a threat analysis for the pizza company database.
3. Create roles for the various types of users in the pizza
database.
4. Create an SQL Server login for a user and assign the user to
a role.
5. Create a view for one of the roles and grant permission to
select from the view to one or more users.
6. Assume you are working for a small bookstore. They have a
database that keeps track of all their inventory and all their
sales and trades with customers. This bookstore also maintains
an online presence with a Web site, where users can
browse the catalog and purchase books using second-party
software to process the payment. There is only one store and
they are located in the downtown area of a city known for
occasional severe earthquakes. Create a disaster recovery
plan for this company.
For Practice 7-11, look at the following stored procedure
and answer the questions that follow.
CREATE PROCEDURE usp_AddRequest
@CourseKey NCHAR(10),
@StudentKey NCHAR(10),
@RequestKey NCHAR(10)
AS
DECLARE @Date DATE
DECLARE @Status NCHAR(10)
SET @Date=GETDATE()
SET @Status = ‘Active’
BEGIN TRAN
BEGIN TRY
INSERT INTO REQUEST(
RequestKey,
CourseKey,
RequestDate,
RequestStatus,
StudentKey)
Values(
@RequestKey,
@CourseKey,
@Date,
@Status,
@StudentKey)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
7. What are the names of parameters in the procedure?
8. What are the names of the variables?
9. What happens to the transaction if there is no error?
10. What happens to the transaction if there is an error?
11. Why do you need a TRY CATCH with a Transaction?
Scenarios
Imo WOOD APARTMENTS
The apartment managers at Wild Wood like what you have
done so far, but as the database takes shape, they have begun
to worry about security. The tenant information should not be
accessible to just anyone. And they would like to keep the financial
information internal, and not let outsiders or other companies
see the details of their operation.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
176 Chapter 8 0 Is It Secure?
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (making a
rent payment, for instance, or a maintenance request).
VINCE’S VINYL
Having shown Vince your work so far, you broach the topic
of security. At first, Vince doesn’t see much need for security
measures, but you point out a few areas that should be considered.
For one thing, Vince probably doesn’t want to share
his list of interested customers. That is valuable information
in itself, and his customers will have an expectation of privacy.
Additionally, the day-to-day financial information concerning
sales and purchases is probably best not available for
general public perusal. You also point out that it is important
that Vince be able to trust his data. He needs to know that no
one has accidentally, or on purpose, messed up his inventory
or sales data.
To do
1. Create tables of the data access needs of Vince’s users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (purchasing
an album, for instance, or recording a customer request).
GRANDFIELD COLLEGE
As with any database, data integrity is important to the software
database at Grandfield College. If they are audited, they
have to show that they know what software they have, how it
is licensed, and on what machines it is installed. Accident and
error are the most likely threats to their data integrity, but it is
always possible that someone might try to purposely disrupt
their data.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (installing a
piece of software, for instance, or processing a software
request).
WESTLAKE RESEARCH HOSPITAL
Security has always been a part of the WestLake Hospital’s
database. In a double-blind study, it is absolutely essential that
no one tampers with the data. Also, patient confidentiality
and the sensitive nature of the study require that the patients’
records and the records of their sessions with the doctors be
kept absolutely private and secure. The researchers are anxious
to see your plan for securing the data.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (making an
appointment, for instance, or letting patients see some of
their doctors’ session notes on them.)
SHOW TIMES: LOCAL SHOWS AND ACTS
Now that you have the database built and ready to go, it is time
to consider the security needs of the databases. Data Integrity is
important. The database won’t be of much use if it’s not trustworthy.
Also, you want to ensure that no one goes in and just
changes things as a joke or with intention of interfering with a
competing venue or band. You also need to think about where
the database is going to be housed and how you are going to
protect it from various potential disasters.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (adding a
new show, for instance, or registering a new fan with
their genre and act preferences.)

Unit IV Project
For this assignment, you will continue to work with the scenario you began in Chapters 1-3. The initial scenarios are discussed on pages 17-18 of your textbook and continued on pages 37-41 and pages 60-61. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)
Instructions:
1. Reread the introduction to your selected scenario on page 17 or 18 and the continuations on pages 37-41 and pages 60-61.
2. Read your scenario for Chapter 4 on pages 79-81.
3. Review the “Deliverables” section below.
4. Review the “Suggestion for Scenarios” section on page 81 of your textbook for important details and ideas to assist you with your deliverables
5. Review Appendix D, Common Relational Patterns, on page 191 of your textbook.
6. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, and page 80. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
7. When you have completed all of the deliverables, compress the files together into a single .zip file for upload.
Deliverables:
1. Complete the numbered tasks for your scenario. The table below shows where you can find each list:
2. Your scenario “To Do” list may ask you to review your work with another student or group. That step is not required for this assignment.
Scenario Page Number for “To Do” Grandfield College Page 80
Unit V Project
For this assignment, you will continue to work with the scenario you began in Chapters 1-4. The initial scenarios are discussed on pages 17-18 of your textbook and continued on pages 37-41, pages 60-61, and pages 79-80. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)
Instructions:
1. Reread the introduction to your selected scenario on page 17 or 18 and the continuations on pages 37-41, pages 60-61, and pages 79-80.
2. Read your scenario for Chapter 5 on pages 100-101.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 101 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, and page 101. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. When you have completed all of the deliverables, compress the files together into a single .zip file for upload.
Deliverables:
1. Complete the numbered tasks in the “To Do” section for your scenario. The table below shows where you can find each list:

Scenario Page Number for “To Do” List Grandfield College Page 101

Unit VI Project

For this assignment, you will continue to work with the scenario you began in Chapters 1-5. Reread the information from each of these assignments at the end of each chapter. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)

Instructions:
1. Reread your selected scenario at the end of all previous chapters.
2. Read your scenario for Chapter 6 on pages 123-124.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 124 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, page 101, and page 124. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. Your final deliverable will be a script file that you create from your database. To generate an SQL Script, right click on the database, go to TASKS on the context menu, and then GENERATE SCRIPTS. In the wizard you should change “Script Data” from “false” to “true” and then generate a script of the database you have built to turn in.

Deliverables:

1. Complete the numbered tasks in the “To Do” section for your scenario, EXCEPT number 6. You DO NOT need to create a data dictionary. The table below shows where you can find each list of tasks:
Scenario Page Number for “To Do” List Grandfield College Page 124 Unit VII Project For this assignment, you will continue to work with the scenario you began in Chapters 1-6. Reread
Unit VII Project

For this assignment, you will continue to work with the scenario you began in Chapters 1-6. Reread the information from each of these assignments at the end of each chapter. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)

Review the Textbook/Course Materials section of the syllabus and appendix B of your textbook for guidance on using the correct software programs.

Instructions:
1. Reread the introduction to your selected scenario at the end of all previous chapters.
2. Read your scenario for Chapter 7 on page 155.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 156 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, page 101, page 124, and pages 155-156. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. Your SQL queries should be created, tested, and then copied and pasted into a Word file to be submitted to your instructor. Contact your instructor if you have any questions.

Deliverables:

1. Review the scenario and complete the task for your given scenario. The table below shows where you can find each list of tasks: Grandfield College Page 155

Unit VIII Project
For this assignment, you will continue to work with the scenario you began in Chapters 1-7. Reread the information from each of these assignments at the end of each chapter. (Again, the “Westlake Research Hospital” scenario will act as an example throughout this course.)
Instructions:
1. Reread your selected scenario at the end of all previous chapters.
2. Read your scenario for Chapter 7 on page 175 to 177.
3. Review the “Deliverables” section below.
4. Review the “Suggestions for Scenarios” section on page 177 of your textbook for important details and ideas to assist you with your deliverables.
5. Read the “Westlake Research Hospital” scenario on page 18, pages 41-45, page 61, page 80, page 101, page 124, pages 155-156, and page 176. Click here to review the suggested answer for this scenario to gain an understanding of what is expected of you on this assignment.
6. When you have completed all of the deliverables, compress the files together into a single .zip file for upload. Contact your instructor if you have any questions.
Deliverables:
1. Complete the numbered tasks in the “To Do” section for your scenario. The table below shows where you can find each list of tasks: Grandfield College Page 176

Chapter 1 Who Needs a Database 17
Things to Look Up
1. Look up Codd’s 12 rules. Choose one of the rules to explain
to your fellow students.
2. Look up the history of SQL. How many revisions of the 5.
standard have there been? What was added in the most
recent one?
3. Use the Internet to look up database-related jobs. Make a
brief report summarizing what you find.
4. A recent trend for major commercial database developers is
to offer free “Express” versions of their databases. Microsoft
has SQL Express, Oracle has Oracle Express, and DB2 has 6.
DB2 Express. Visit the company Web sites and look up these
Express editions. What features does each one have? What
limits do they have? How do they compare to each other?
For some time there have been attempts to move beyond
relational databases, to find some new data model. One
direction has been to move toward object-oriented databases.
Another area of research is into XML-based databases.
Choose one of these to look up and write a brief summary of
what the model entails and what is the current status of the
model.
Look up statements of work. What are some additional elements
that can be included?
Practices
1. Think about keeping a home budget. Would it be better
to keep the budget in spreadsheets or to create a budget
database? Write a couple of paragraphs that describe your
choice and at least three reasons to justify it.
2. Think of a small business or nonprofit that you know that
could use a database. Explain why you think a database
would help the business. List the benefits the business or
nonprofit would gain from a database.
3. An entity is something the database is concerned with. For
instance, a movie rental business would probably have an
entity called DVD. Attributes are things that describe the
entity. Make a list of possible attributes for a DVD entity.
4. You are going to interview a small business owner about
creating a database for his sandwich shop and bakery. Make
a list of questions for this initial interview. Remember at this
point you just want the big picture and major requirements.
Don’t get too deep into the details.
5. Think about the sandwich shop and bakery in Question 4.
List what you think the major topics would be.
6. A dentist office wants a database to track its appointments.
The specifics of what they want to track are as follows:
a. All customers of the dental office
b. Customer appointments
c. Which dentist serves each customer at the appointment
d. Which assistants assist each dentist
e. In brief, what services were provided at the appointment
f. The database will not track bills and payments (they
have a separate software for this purpose)
Write a statement of scope for the dental office database.
Scenarios
These scenarios are designed to give you the opportunity to
experience database development from beginning to end. Each
has its own unique challenges. The scenarios can be pursued
individually or in small groups. I would suggest choosing one
scenario that interests you to follow throughout the term. Later,
if you are so inclined you can return and work through some of
the others.
7. List the major themes for the dentist office database in
Practice 6.
8. How long do you think it would take to gather the information
needed to make the dentist office database in Practice 6.
_Discuss_what steps_you think would be involved and how
long it might take to build the database.
9. Look around the school or think of some businesses or nonprofits
with whom you are familiar. Identify at least one
situation in which a database could be of help.
a. Describe why a database would improve the situation.
b. Describe what the major topics of this database would
be.
c. Write a statement of work for this database.
10. An instructor has been keeping all his grade books in Excel
for years. He has a separate spreadsheet for every course. In
the spreadsheet he tracks the scores for every assignment
and test and then assigns term grades based on the overall
averages. Whenever a former student contacts him requesting
a letter of recommendation or whenever the administration
requests information concerning a student in a previous
term he has to open and search several spreadsheets to get
the student’s information.
a. What are some of the advantages a database would
have over the current system for this instructor?
b. What would be some of the major topics for the
database?
c. Write a statement of work for the preceding database.
WILD WOOD APARTMENTS
Wild Wood Apartments owns 20 different apartment complexes
in Washington, Oregon, California, and Idaho. Each
apartment complex contains anywhere from 10 to 60 separate
apartments, of varying sizes. All apartments are leased with a
6 month or yearlong lease.
18 Chapter 1 Who Needs a Database
It is the company’s practice to hire one of the tenants to
manage each apartment complex. As manager, he or she needs
to admit new tenants to the building, collect rents from existing
tenants, and close out leases. The manager also needs to maintain
the apartments by executing any repairs, replacements, or
renovations. These can be billed back to the parent company.
For acting as manager, the tenant gets free rent and a stipend.
The stipend varies depending on the size of the apartment
building.
Each manager is expected to send a report to the Wild
Wood Apartments company headquarters in San Francisco
every quarter. This report summarizes the occupancy rate, the
total revenues in rent, the total expenses in maintenance and
repairs, and so on. Currently, managers fill out a paper form
and mail it back to headquarters. Many apartment managers
have complained that preparing this report is a very difficult
and time-consuming process. Also, the managers at corporate
headquarters have expressed concerns about the accuracy and
_verifiability of the reports.
To allay these concerns and to improve the ease and efficiency
with which the apartment managers conduct their daily
business, the company is proposing to develop a centralized
database that can be used by the managers to track the daily
business of their apartment building and to prepare their
reports
To do
1. List the major topics for this database.
2. Write a draft statement of work. Include a brief history,
a statement of scope, objectives, and a preliminary
timeline.
3. Documentation: Start a notebook, either electronically
or physically, to record your progress with the scenario
database. Add the statement of work and any notes to
the notebook.
VINCE’S VINYL
Vince Roberts runs a vintage record shop in the University district.
His shop sells 45’s, LPs, and even old 76 RPM records.
Most of his stock is used—he buys used vinyl from customers
or finds them at yard sales and discount stores—but he does sell
new albums that are released on vinyl. For a couple of years, he
has kept most of his inventory either in his head or in a spiral
notebook he keeps behind the sale counter. But his inventory
and his business have grown to where-that is far from sufficient.
Vince is looking for someone to make him a database. He
knows he needs to get a better handle on several aspects of
his business: He needs to know the extent and condition of his
inventory. He needs to know the relative value of his inventory—some
records are worth a fortune; some are nearly worthless.
He also needs to track where, from whom, and for how
much he purchased his stock. He needs to track his sales. He
often is not entirely sure how much money he has spent or how
much money he has earned.
In addition he would like to allow customers to make specific
requests and notify them if a requested item comes in. More
generally he would like to make an email list of interested customers
in order to let them know about new items of interest.
Someday, he would like to expand his business online. But
he knows he needs to have everything under control before then.
To do
1. List the major topics for this database.
2. Write a draft statement of work. Include a brief history,
a statement of scope, objectives, and a preliminary
timeline.
3. Documentation: Start a notebook, either electronically
or physically, to record your progress with the scenario
database. Add the statement of work and any notes to
the notebook.
GRANDFIELD COLLEGE
The law requires that any business, including a school, track
its software. It is important to know what software the school
owns, in what versions, and what the license agreement for that
software is. There are several different licensing schemes. The
least restrictive is a “site” license that allows an institution to
have a copy of the software on any machine on the business
property. Other licenses specify a certain number of active copies
for an institution but don’t worry about which machine or
user has the copy. The more restrictive licenses do specify one
copy per specific machine or user.
Whatever the license agreement for particular software, it is
essential for the institution to know which software is installed
on which machine, where_thatmachine_isiocated,ancLwhich—
users have access to that machine. It is also important to track
when the software is uninstalled from a machine, and when a
machine is retired.
An additional useful feature of any software-tracking
database would be to track software requests from users to
determine (1) if a copy of the software is available and (2) if
it is something that should be purchased. All installations are
reviewed and must be approved.
For now, the school just wants the database to track faculty
and staff computers and software. Software for student
machines is a separate and complex issue and will be treated as
a separate project at a later time.
To do
1. List the major topics for this database.
2. Write a draft statement of work. Include a brief history,
a statement of scope, objectives, and a preliminary
timeline.
3. Documentation: Start a notebook, either electronically
or physically, to record your progress with the scenario
database. Add the statement of work and any notes to
the notebook.
INESTLAKE RESEARCH HOSPITAL
A hospital is conducting a double blind test of a new depression
drug. It will involve about 20 doctors and about 400 patients.
Half of the patients will get the new drug and half will get traditional
Prozac. Neither the doctors nor the patients will know
who is getting which drug. Only two test supervisors will know
who is getting what. The test will last about 18 months. Each
doctor will see 20 patients initially, though it is expected some
patients will drop out over time. Each patient will be coming
in twice a month for a checkup and interviews with their doctor.
The drugs will be dispersed in a generic bottle by the two
supervisors one of whom is a pharmacist.
payment amounts into a spreadsheet. While he was working,
a tenant came in and handed him a check. Joe thanked the tenant
and added the check to the pile. When he had finished, he
checked his list against a list of tenants. He told me that three
had not paid their rent yet.
He called each of the three. The first did not answer, so he
left a message. “I am not too worried about him,” Joe told me.
“He isn’t always on time, but he always pays within the 5-day
grace period.”
I asked about the grace period. Joe answered, “The company
allows a renter to be up to 5 days late without a penalty. If you
pay after that there is a $100.00 penalty tacked on to the rent.”
He called the second renter. She was at home and asked if
he could wait until the 10th. Joe said OK and then explained to
me; “She’s an older woman and dependent on Social Security
and retirement checks. I give her a little more leeway. The company
lets me because she has lived here forever and has always
been a good tenant. This last one though is just no good.” He
picked up the phone and called. He got no answer, and there
was no answering machine. Joe told me that he was about ready
to evict this last tenant. He is habitually late, and he is actually
2 months behind in his rent. Joe tells me how difficult it is to
actually evict someone.
While he is telling me stories about past evictions, the phone
rang. A woman in apartment 211 told him that her stove wasn’t
working. Joe opened a second spreadsheet and entered some
Chapter 2 0 Gathering Information 39
of the details. He also wrote some notes on a pad of paper. He
reassured the woman that he would deal with it quickly and
promised to come by in the afternoon.
After 4 hours, I thanked Joe for his time and left him to his
lunch.
To do
1. Make a list of questions that you would ask about these
forms and reports.
2. Identify the stakeholders for Wild Wood Apartments.
3. Create a plan for an hour-long interview with representatives
of these stakeholders. Then meet with the instructor
to discuss possible answers to the questions.
4. Create a questionnaire of at least five questions for the
managers of the 20 apartment buildings.
5. Look at the Job Shadow Report. Do you see any exceptions
to the general rules? Do you see any new business
rules uncovered? What additional questions arise from
the report?
VINCE’S VINYL
Vince hasn’t kept very complex records, but he does have a
few things he can show you. The first thing he has is an example
of the notes he takes when he purchases an album from a
customer.
Date 5/14/2013
Seller’s Name Seller’s Phone Number Album Notes Condition Paid ($)
John Raymond 206.555.2352 Rubber Soul Amer. Not British vers. 2nd edition,
good Sleeve
fair 4
Marilyn Taylor 206.555.0945 Led Zepplin IV Not orig. Sleeve damaged, vinyl good good 4.75
Jennifer Louis 206.555.4545 Gift of the flower to
the Gardner
Rare Donovan, box set, box condition
poor, but vinyl excellent
excellent 12.25
Laura Hall 206.555.2080 Dark Side of the Moon good 4.45
Here is an example of a sale to a customer:
Date
Customer
5/12/2013
Album Price ($) Tax ($) Total ($)
John Larson Dylan, Blond on Blond 19.95 1.65 21.60
Tabitha Snyder America 5.95
Joni Mitchell, Blue 6.25
Joan Baez, Ballads 4.20 1.36 17.76
Brad Johnson McCartney, Venus
and Mars
5.00 0.42 5.42
Maureen Carlson Decembrists,
The Crane wife
15.50
Muddy Waters 7.75 1.92975 25.18
Job Shadow Report
I sat with Vince for a full day of work. The morning was quiet,
and Vince spent the time sorting through a stack of albums that
he had purchased earlier in the week. He took each one out of
the sleeve and inspected it carefully. “Sometimes I catch things
that I didn’t see when I actually purchased it,” he explained
to me. “It is too late now, of course, to do anything about it,
but I want to be fair to the people I sell it to.” He put a sticker
on the cover and put “good” and a price of $6.50. I asked him
about how he classified and priced things. He told me he had
four levels: mint, good, fair, and poor. Mint was only for things
that were nearly perfect. Good meant there were no scratches
and the vinyl was not warped and not too worn. Fair meant
the vinyl was a bit more worn and might have a light scratch or
two. Poor meant the vinyl was scratched and probably warped.
He didn’t buy poor vinyl unless it was an extremely rare album.
Prices were based on what he thought the album would bring.
He based it mostly on experience.
After a while, a customer came in. He asked if Vince had
seen a copy of an old album. He commented that he didn’t think
it had ever made the transition to CD. Vince said he had seen it,
but he didn’t have a copy currently, but if the customer wanted
he would take his name and number and let him know when
he next got a copy. The customer agreed and then, after looking
around for about 20 minutes, returned to the counter with five
albums. Vince wrote down each album title and the price and
then added the prices on a hand calculator. The total came to
$35.50. Vince said, “Make it thirty, and we’ll call it good.” Vince
40 Chapter 2 • Gathering Information
explained that it was good for business. It made the customer
feel good, and they were more likely to come back. Several
more customers came in, and their transactions followed a similar
pattern.
In the afternoon, a customer came in with a stack of albums
he wanted to sell to Vince. Vince went through the albums, taking
each one out of its sleeve and inspecting it. In the end, he
split the albums into two piles. He told the customer he was
interested in the first pile of about 12 albums and would *offer
him $20.00 for them. The customer pulled one album out of the
pile Vince had selected and said “I thought this one might be
worth a little more. It is a first print.” Vince looked at it again.
“Yes it is, but it is scratched and only in fair condition. Still, I’ll
make it $25 if that makes it seem more fair to you.” The customer
agreed. Vince told him he wasn’t really interested in the
second pile of albums. The customer could either take them
back or Vince would put them on his 5-for-a-dollar pile. The
customer chose to leave them.
Vince put the albums in a pile by his desk. Several more
customers came and went. Vince chatted pleasantly with all of
them. Several purchased an album or two. At about four, Vince
turned the open sign in his window to closed, and I thanked
him for his time and left.
1. Study Vince’s sample notebook entries. Make a list of
questions you would ask about the data in them.
2. Identify the stakeholders in Vince’s record store.
3. Prepare an interview with Vince and two of his best
customers: one who both sells albums to Vince and
buys, and one who mostly just buys. Then meet with
the instructor to discuss possible answers to the
questions.
4. Create a questionnaire for those who sell albums to Vince
about changes they would like to see in the process.
5. Look at the Job Shadow Report for Vince. Do you see arty
exceptions? What additional business rules do you see?
What additional questions does the report raise?
GRANFIELD COLLEGE
The software management team has several spreadsheets to
keep track of software. They show you several samples. The
first is just a listing of software:
Software Version Company License Type
Windows Vista Business, Service Microsoft MS Site
Pack 2
MS Office 2007 Microsoft MS Site
Visual Studio Professional 2008 Microsoft MS Instructional
PhotoShop CSS3 Adobe Adobe1
Filelilla 5 Filelilla Open Source
German 2.5 LanguageSoft LanguageSoft1
The second is a key to the different licensing agreements
and types:
To do
License Type Start Date End Date Terms Pricing Pricing Unit
MS Site 7/1/2009 7/1/2013 Can install as many copies as needed on campus and on
laptops controlled by the school. Includes all service patches,
updates, and version changes
12500 5 yrs
Ms Instructional 7/1/2010 7/1/2015 Used for instructional purposes only. Cannot be used for school
development projects
3000 5 yrs
Adobe1 7/1/2009 7/1/2015 Reduced price per installed copy, maximum of 25 active copies 450 Per active copy
Open Source 7/1/2009 7/1/2020 Free for use as long as registered 0
LanguageSoft1 7/1/2012 7/1/2016 25 copies 5200 For 25 copies
Here is an example of the list of who has what software:
CCS Number Location Assigned User
3214
Software
Rm214
Install date
Cardwell
Rmv Date
Vista Business 5/3/2013
Ms Office 5/3/2013
PhotoShop 6/4/2013
DreamWeaver 6/4/2013
CCS Number Location Assigned User
3114 Rm212 Larson
Software Install Date Rmv Date
Vista Business
MsOff ice
Visual Studio Pro
DreamWeaver
4/15/2013
4/15/2013
6/12/2013
6/14/2013 7/12/2013
Chapter 2 0 Gathering Information 41
And, finally here is sample of a request for new software:
Requests
CCS Number User Request Date Software Reason Response Res Date Status
2123 Johnson 5/20/2013 Camtasia lam conducting several online classes.
I need to be able to create visual
demos to post to the class Web site
We don’t currently have 5/24/2013 Pending
a license for Camtasia but
will explore acquiring one
Job Shadow Report
I spent the day on 4/12/2013 following Sheri, a member of
the software management team at Grandfield College. The
first thing she did after settling into her office was check a
spreadsheet that listed pending installations. She showed me
the list and told me that she had about six installations to do
that morning. She also noted that it was the most boring part
of her job. “Nothing like watching the progress bar on the
monitor for hours at a time,” she said. Next, she checked her
emails. There were three requests for additional software. She
opened a spreadsheet and entered the request information.
She told me that she would check later to see if the school
had the software or if it was something they would have to
purchase-Af-it-was-a purchase,=she would-have-to-get-pernus=
sion. She replied to each of the emails to acknowledge their
request.
After noting the requests, she looked again at the installation
to be done. She went to a cupboard and pulled out some
disks. She told me that some software can be installed from
a network drive, but for some she has to bring the media.
She also grabbed a notebook. We went to the first office. She
spoke for a few moments with the woman who occupied the
office. They laughed at a few things. Sheri said that with luck
the installations should take no more than 30 minutes. The
woman left the office to let Sheri work. Sheri logged into the
computer as administrator and slipped in a DVD. She started
the install.
I asked her about the notebook. Sheri told me that she carried
it for two reasons. If there were any problems with the
install that she couldn’t solve, she would write down the error
messages and take them to the other techs to resolve. She also
would note in the book whether the installation was a success
or not. She didn’t put it in the spreadsheet until the installation
was complete and successful.
The rest of the morning, Sheri moved from office to office
installing software. On that day, at least, there were no major
installation issues. While we waited, she told me about other
days that didn’t go so easily. She told me about how difficult
it could be to troubleshoot a bad install, and how obscure and
undocumented settings could require hours of research before
they were discovered and resolved.
The installations were finished by lunch. After lunch, Sheri
checked with the department receiving new software and
packages. There were several that had arrived. Sheri carefully
unpackaged each arrival and noted it in a spreadsheet. Then
she checked the licensing agreements. Some she knew, others
she had to check, often looking up the licensing agreement
online. “Everybody is different,” she told me. “Some let you
install the software anywhere on-site. Some will only allow
a certain number of copies. Some can be placed on a server,
while some only allow client installations. Some are tied to a
particular user. It would make my life easier if things were
consistent.”
Late in the afternoon, Sheri received a call for an instructor
requesting disks for a piece of software. She told him
“sure,” if he would come up and get it. He arrived at the door
shortly afterward. She gave him the disks and made him sign
for them in a notebook. “I’ll have them back to you tomorrow
morning,” he said. Sheri explained, “There are two or
three instructors who have administrative privileges on their
machines. They do their own installations and their own
suppprt.” I asked if they track the software on those instruc- _ _
tors’ machines. Sheri told me that they do as best as they
can, but the instructors can do pretty much as they want. To
get the admin privileges, they have to sign a release saying
they won’t violate any licensing agreements and that they
accept the fact that the school IT staff will not support their
computers.
Following this, it was time to quit. Sheri shut down her computer.
I thanked her for allowing me to follow her and wished
her “good evening.”
To Do
1. Study the samples given earlier. Make a list of questions
you would ask about the data in them.
2. Identify the stakeholders in the software-tracking
system.
3. Prepare a plan for a 1-hour interview with representatives
of the stakeholders listed earlier. Then meet with the
instructor to discuss possible answers to the questions.
4. Create a questionnaire for faculty and staff about changes
they would like to see in the request process.
5. Review the job shadowing report. Do you see any exceptions?
Do you see any additional business rules? What
additional questions does the report raise?
WESTLAKE RESEARCH HOSPITAL
The drug study is unique in many ways. For one, the forms and
the type of information they capture are more complex. For
another, privacy rules make it difficult to shadow doctors or
researchers. But, still, if you are going to create a database, you
must begin to gather the requirements and figure out what data
are needed to be tracked.
Here is the Initial Medical Form that each patient is asked
to fill out:
42 Chapter 2 . Gathering Information
Initial Medical History Form
Name Date
Birth Date
Address
City State Zip
Phone Email
List any prescription or nonprescription medicines you are currently taking.
List any known allergies to medicines.
Have you ever been told you had one of the following?
Lung disorder: 0 yes 0 no
High blood pressure: 0 yes 0 no
Heart trouble: 0 yes 0 no
Nervous disorder: 0 yes 0 no
Disease or disorder of the digestive tract: 0 yes 0 no
Any form of-cancer 0-yes- 0-noDisease
of the kidney: 0 yes 0 no
Diabetes: 0 yes 0 no
Arthritis: 0 yes 0 no
Hepatitis: 0 yes 0 no
Malaria: 0 yes 0 no
If you answered yes to any of the above, please explain:
Disease or disorder of the blood? (describe)
Any physical defect or deformity? (describe)
Any vision or hearing disorders? (describe)
Any life-threatening conditions? (describe)
How would you describe your depression?
a. Mild and continuous
b. Mild but intermediate
c. Moderate and continuous
d. Moderate but intermittent
e. Severe and continuous
f. Severe but intermittent
When were you first diagnosed with depression?
Which of the following symptoms have you experienced?
O Sleep difficulties
O Loss of appetite
O Loss of libido
O Inability to leave house
O Anxiety in social situations
O Thoughts of suicide
(continued)
Chapter 3 • Requirements and Business Rules 59
Although it is not listed, students have student IDs, which could be used to uniquely
identify each student. Most tutors are also students and would have a student ID, but
not all tutors are students. Perhaps there is some sort of employee ID. She will have to
ask Terry. Each course has a unique name so that could be a potential key for that entity.
For the session, the session date or the session time, perhaps in combination,
could be a key, but that wouldn’t really be unique because different tutors could have
sessions on the same day at the same time. If the tutor ID was added to the key, that
could be unique.
After all the analysis, Sharon feels ready to get to work on the logical design of the
database.
DOCUMENTATION
Requirements and business rules are an essential part of the documentation of any
database. A developer needs to refer to them many times during the development
process. She needs to constantly check to see if the database is meeting all the requirements.
Is anything being left out? Are there elements that weren’t in the initial requirements?
And, if there are, should they be added to the requirement list, or should they be
removed? When the database is completed, the requirements and business rules guide
how the database is tested. The developer should look at each requirement and rule and
make sure the database satisfies it. Terry, for instance, requires the database to be able
to produce reports with unduplicated counts of students. The database must be tested
to make sure this is possible.Tf itJsnt, thenitmustbe_adjusted
Additionally, anytime someone needs to change the database, make an addition,
or replace it all together, that person will need to review the original requirements to
see if the changes add to them or alter them in some way. If no one documented the
original requirements and rules, the person will have to recreate them by talking to the
users of the database. He or she will, in short, have to do the whole process of gathering
requirements over again. If the database has been in use for a long time, this can be difficult.
Often, many of the people involved in the original development of the database
have retired or left for other jobs. It is possible that no one will remember exactly the
reasons for creating the original database.
The requirements and business rules for a database should be clearly marked and
stored with the other database documentation.
The initial attempts listing nouns and dividing them into attributes do not necessarily
need to go into the formal database documentation, but it is a good idea to keep
them and other project notes in a folder or notebook of some kind. It is always useful to
be able to review your notes and revisit why you made the decisions you did. You may
find that you need to modify the original ideas based on later evidence.
Things We Have Done
In this chapter, we have
• revisited the problem domain by reviewing the issues with
the current system developed a list of requirements for
each user
• reviewed the business rules for the tutoring database
• reviewed the materials collected in the previous chapters
and extracted nouns that may become entities and attributes
Things to Look Up
1. Look up “Requirement Analysis” on the Web. What kinds
of topic headings do you find?
2. Look up two or more definitions for “Business Rules.”
• organized the nouns into preliminary entities and
attributes
• looked for attributes that could serve as candidate keys, that
is, attributes that could potentially work as primary keys for
the entities
3. Look up an article on the Web that discusses natural versus
surrogate keys in databases. Which does the author prefer?
4. What are some additional plusses or minuses of each?
60 Chapter 3 e Requirements and Business Rules
Vocabulary
Match the definitions to the vocabulary words:
1. User access
2. Server
3. Surrogate key
4. Stakeholder
5. Requirement
6. Natural key
7. Client
8. Trigger
9. Composite key
10. Business rule
11. Problem domain
Practices
a. A program that requests a service
b. A key that consists of more than one attribute
c. A program in SQL that is triggered by a database event
d. A program that offers a service to requesting programs
e. A key based on one or more “natural” attributes of an entity
f. A rule about how data are acquired, stored, or processed
g. The general problem area with which a database is concerned
h. An artificial key, often just an incremented number
i. Something a database must do to meet a business need
j. A person or program that makes some use of the database
k. The permissions a user has to take to view database objects and data
Use the following scenario for each of the practice exercises:
You have been asked to build a database for a pet foster
and adoption shelter. The agency is a nonprofit that takes in
stray or abandoned pets and places them with foster caregivers-until-the-pet-is-
adopted-Foster-caregivers- are volunteers;
though they must first be screened. The database needs to
track all animals in its care, their species, breed, name, and
condition. It also needs to track all approved foster caregivers
and the animals currently in their care. Foster caregivers
are also supposed to turn in monthly reports on the animals
in their care. The database also needs to track the adoptions of
the animals.
Currently, volunteers come into the shelter and fill out a
paper form. After a background check, they are added to a file.
Some volunteers complain that they are never contacted again.
The shelter staff admits, they tend to go with foster caregivers
they know, and some people get forgotten in the file. The
shelter has also occasionally lost track of an animal in foster
care when the caregiver failed to turn in the monthly reports.
Another recurring problem is that when someone comes into
the shelter looking to adopt, it is not always easy or even
possible to let them know about all the animals available for
adoption.
Scenarios
Ideally, the shelter would like people to be able to register
as a volunteer online. They would like to be able to call up a
list of all available foster volunteers. They would also like to be
able to pull up all the animals of the kind a potential adopter
is-interested-in and-know-exactly-where those-animals-are-and
who is caring for them.
1. Make a list of some of the major issues with the current
system used in the shelter.
2. Identity who the major stakeholders, are and list them.
3. Would animals be stakeholders in this ‘database? Explain s„
why or why not.
4. Make a list of requirements for each of the stakehotciers
showing how he or she would interact with the databak:
5. Make a list of business rules for the shelter.
6. What might be some of the shelter database security issues?
7. Make a list of all the nouns in the description of the
shelter.
8. Take the list from practice5, and determine whlat you
think would be the major entities.
9. List the attributes for each of the entities you listed in
practice6.
10. Identify some candidate keys.
WILD WOOD APARTMENTS
The Wild Wood Management team is ready to see some results.
You have a meeting with them at the end of the week. It is time
to analyze and organize all the information. Look back at the
material from earlier chapters.
To do
1. Make a list of issues with the current system.
2. Make a list of the database requirements for each stakeholder
involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms, and make a list of all the
nouns in them. Do the same for the interview, the questionnaire,
and the Job Shadow Report. Then set up some
preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
VINCE’S VINYLS
You are eager to show Vince some progress on the database.
You sit down to analyze all the materials you have gathered to
see if you can make some sense of them. Make sure you review
the material in the previous two chapters.
To do
1. Make a list of issues with the current system.
2. Make a list that shows the database requirements for
each stakeholder involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms and make a list of all the
nouns in them. Do the same for the interview, the questionnaire,
and the Job Shadow Report. Then set up some
preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
GRANDFIELD COLLEGE
It is imperative that the college get the software tracking database
online as soon as possible. You have assured the management
team that you will be able to show some progress
very soon. It is time to set down and review all the forms and
materials.
To do
1. Make a list of issues with the current system.
2. Make-a list of the database requirements for each stakeholder
involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms and make a list of all the
nouns in them. Do the same for the interview, the questionnaire,
and the Job Shadow Report. Then set up some
preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
WESTLAKE RESEARCH HOSPITAL
The drug study is set to begin in just a few months’ time. It is
important to make some progress toward the database. It is
time to gather all the materials you have collected and try to
make some sense of them.
To Dos
1. Make a list of issues with the current system.
2. Make a list of the database requirements for each stakeholder
involved in the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the forms and make a list of all
the nouns in them. Do the same for the interview, the
Chapter 3 o Requirements and Business Rules 61
questionnaire, and the Job Shadow Report. Then set up
some preliminary entities and attributes.
6. Identify some candidate keys.
7. Documentation: Store the list of the requirements and
business rules in your database notebook.
SHOW TIMES: LOCAL SHOWS AND ACTS
You want to get the database to track bands and venues in place
as soon as possible. There are a lot of interesting shows scheduled
in the not too distant future. You set down to review the
materials you have collected so far. Look back at the previous
chapters:
To do
1. Make a list of the issues with the current way of getting
the word out about shows.
2. Make a list that shows the database requirements for
each stakeholder who will be involved with the database.
3. Make a list of business rules.
4. List some major security rules for the database.
5. Take a look at each of the documents and make a list of
all the nouns in them. Do the same for the interview, the
questionnaire, and the Job Shadow Report. Then set up
some preliminary entities and attributes. _
6. Identify some candidate keys.
7. Documentation: Store the list of database requirements
and business rules in your database notebook.
SUGGESTIONS FOR SCENARIOS
Review all the documents and interviews from Chapter 2. It
will probably be necessary to talk with your instructor or other
students to answer some of the questions about the scenarios
that have not yet been answered. A certain amount of invention
is expected here.
Look at the requirements in terms of each user or actor. It is
much easier to do it this way rather than trying to just make a
general list of requirements. The actor gives you a clearer focus
on what the database needs to do in a particular instance. The
same holds true of the database security. It is much easier to
understand in terms of each actor’s access needs.
When making the initial list of nouns, don’t try to distinguish
between entities and attributes. Just list them in the order
you encounter them. Save the analysis until you are done.
When you do the analysis, remember entities are major
themes or elements. They will tend to stand out. If you find a lot
of words clustered around a single topic, the topic is likely the
entity and the words clustered around it are probably attributes.
It is good to remember that this is a very preliminary stage
of analysis. There aren’t any absolutely right or wrong answers
at this stage.
Databace Design
ENTITY RELATION DIAGRAMS
Having organized her materials and determined the business rules, in this chapter, Sharon begins the logical
design of the tutoring database. Using Microsoft Visio, she defines the database entities, their attributes, and
tre reletiOnships a-m-o-n-g—thenT. 4
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
Use the database modeling template in Microsoft Visio
Create entities and add attributes
Determine the appropriate relationship between entities
3 Resolve many-to-many relationships with a linking table
ESIGNING THE DATABASE
Sharon is ready to prepare the logical design of the database. The logical design, she knows, is separate from any
consideration of which DBMS the database is going to be developed on. It doesn’t take into account how the files
will be stored or accessed. It ignores any features or limitations of the target DBMS. It is focused purely on the
logical structure of the entities and their relationships with each other.
THINGS TO THINK ABOUT
The logical design of a database is the same no matter what
the RDBMS is going to be.
Physical design is specifically tailored to the features and
limits of a particular RDBMS.
What is the advantage of separating the logical from the
physical design?
LOGICAL DESIGN
The entity relation design without
regard to what RDBMS or system it
will be on.
PHYSICAL DESIGN
The design adapted to the RDBMS
and system constraints and features.
62
For this process, she is going to use crow’s foot database notation template in
Microsoft Visio and create a new Entity Relation Diagram or ERD. (For a complete
description of opening the entity diagram in Visio, see Appendix C). There are other
templates available, each with its own advantages and disadvantages, but this is the
one she learned and it makes sense to stick with it.
Chapter 4 Database Design 79
FIGURE 4-39 Recipe and
Ingredient Entities
Recipe Ingredient
ra Recipe Key • IngredientKey
5. Create a diagram that shows how you would resOlve the
relationship in Practice 4.
6. An instructor has decided that he needs a relational database
to store grades in. He has defined the following three
entities: Student, Course, and Assignment. What kind of
relationship exists between these entities? 9.
7. Create an ERD for the instructor’s database. Don’t worry about
the attributes, but give each entity a primary key attribute.
Remember to watch out for many-to-many relationships.
8. A dentist office has three dentists, two hygienists, five den- 10.
tal assistants, and two administrative assistants to maintain
the office paper work. They are creating a database to track
Scenarios
appointments and also to track who works with each patient.
So far the database developer has defined the following entities:
Employee (which includes all categories of employee
including the dentists), Customer, and Appointment. Which
entities have many-to-many relationships?
Create an ERD that shows the relationships among the entities
in the dentist office in Practice 8. Remember several
employees (a dentist, an assistant, a hygienist, and so on.)
can be involved in a single appointment for a customer.
Look at the diagram for Practice 8. Identify which entities
are domain entities, which are linking entities, which are
lookup, and which, if any, are weak entities.
INILD=WOOD=APARTMENTS
The managers at Wild Wood Apartments are anxious to see
some progress on their database. They have answered your
questions and now want to see some results. They really want
the new database to be in place before the beginning of the new
fiscal year in July. It is time to design the database.
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign
keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the Wildwood
Apartments business model represented by domain
entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)? Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tabls used for attnbutes that-have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
VINCE’S VINYL
Vince is convinced he is losing money on several of his transactions.
He is anxious to get the new database in place to help
him get control over his business. He has been polite, but keeps
checking on your progress. It is time to show some results.
Create a logical design of Vince’s database. Use the following
steps:
To do
1. Review all the requirements and business rules that you
have gathered from your interviews and after reviewing
Vince’s records.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the Vince’s business
model represented by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
80 Chapter 4 Database Design
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)?Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
GRANDFIELD COLLEGE
A team from the Software Alliance could show up any day.
The IT services manager is eager to get the tracking database
in place. It is time to show some progress. Create the logical
design of the database following these steps:
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all thentities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the software tracking
system represented by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)? Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
WESTLAKE RESEARCH HOSPITAL
It is imperative that the database be ready before the actual
clinical trials begin. The staff at Westlake is anxious to see some
results. It is time you show them the logical design of their database.
Follow these steps:
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign
keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the major components of the clinical trial represented
by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)?Is
the appropriate entity is defined as the one side of a
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
. Are-lookup-tables-used for-attributes-that-have-a-set- –
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
SHOW TIME LOCAL VENUES AND BANDS
The artists and venues you have talked to are really anxious to
see how far you have gotten on the database. It is time to get
down to business and design the database.
To do
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that
exist between them.
3. Create a logical model using crow’s feet notation in Visio
or hand draw it on graph paper if you prefer.
4. Add all the entities and their attributes. You don’t need
to worry about data types for now.
5. Identify the key fields for each entity and the foreign
keys.
6. Analyze the diagram. Identify which role (i.e., domain,
linking, lookup, or weak) each entity plays in your
database.
7. Have another student or a group review it for the
following:
a. Are all the elements necessary to track shows and
artists represented by domain entities?
b. Does each entity contain the appropriate attributes to
fully describe it and meet the business rules you have
gathered so far?
c. Does every entity have an appropriate primary key
defined?
d. Are all many-to-many relationships resolved into
one-to-many relationships by linking tables?
e. Are the relationships valid (no cross relationships)? Is
the appropriate entity is defined as the one side of a
Normalization and
Design Review
Sharon takes her entity relation diagram to her database professor, Bill Collins. Together they review it for
completeness and conformity to the first three normal forms. Then Sharon takes the design to Terry for a final
discussion and review before beginning the physical design of the database.
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
O Evaluate an entity against the first three normal forms
Ei Remove all repeating lists or arrays (First Normal Form)
O Remove functional dependencies (Second Normal Form)
O Remove all transitive dependencies (Third Normal Form)
O Understand the importance of design review
THE DESIGN REVIEW
Sharon knocks on Professor Collins’s door early in the morning. He greets her and offers a chair. He has the
diagram printed out with a few hand-written notes and arrows. He shows her the diagram and begins to explain
his notes. “This is a pretty good diagram. You have all the basic elements in place.”
“I owe it all to what I learned in your class.”
“Thanks.” He looks at the diagram, “I think we should begin by looking at the entities and making sure
they are all properly normalized. Then we should check to make sure all the relationships are correct, and
finally, we can discuss whether the diagram completely captures everything needed to meet the business
requirements.”
“Sound’s good. Let’s start.”
“OK, let’s start with normalization. First, we will see if it conforms to the First Normal Form.”
Things You Should Know
Normalization
Normalization is the process of removing anomalies and redundancies from database design. There are three specific kinds of
anomalies that can occur in database design:
NORMALIZATION
• Insertion anomalies
• Update anomalies
The process of removing anomalies
and redundancies from database
• Deletion anomalies design.
82
one-to-many relationship? Do the tables have appropriate
foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set
list of values?
8. Documentation: Be sure to store your ERDs in your
database notebook.
SUGGESTION FOR THE SCENARIOS
These scenario exercises are probably the most difficult in the
book. The first suggestion is to not panic. Creating ERDs is an
iterative process. No one expects you to have a perfect diagram
Chapter 4 Database Design 81
on the first attempt. The trick is to add entities one at a time.
Don’t try to imagine the whole diagram all at once. Look at each
entity separately. Does it have the appropriate attributes? Is the
primary key defined? After the main entities are on the diagram,
look at the relationships between two entities at a time. What
kind of relationship do they have? Do you need a linking table?
Also check for other such issues. Remember, also, that some
entities have no direct relationship between them. Don’t fall into
the trap of trying to relate every entity to every other entity.
Discussion helps. Others can see issues and approaches that
you might have missed. It is always good to have another pair
of eyes looking over your work.
Chapter 5 o Normalization and Design Review 99
which tutors were currently tutoring versus which ones were no longer tutoring. It is
possible that you could get the same information by querying the Session tables. If a
tutor doesn’t have any current or future dates listed, will he or she be inactive?”
“That wouldn’t always be true. A tutor could be active but not have scheduled
anything for the next two weeks. I think the status field is better. What is the TutorKey?”
Sharon smiles, “I am not entirely sure. A student has a student ID and that can be
the StudentKey. Most tutors are students but not all of them. Do you create an identifying
number?”
“Yes. We give all tutors an employment ID. If they are a student, it is the same as
their student ID; if not, we give them one that looks just like a student ID.”
“Good. That makes that easy.”
They review each of the remaining entities. Terry has questions for each, but after
the full review, she is satisfied that it captures all the information that she will need.
Sharon thanks her. “Now I am ready to actually build the database. We will need
to get together again to decide what we want to build it in.”
Terry looks down at her calendar. “How soon do you want to meet?”
“How about Monday? I think I know what we should use, but I would like to do
a little research.”
“OK, how does 9:00 AM work?”
“That should be fine.”
DOCUMENTATION
It is useful to keep multiple versions of the entity diagram, noting changes made to DENORMALIZATTON
conform to normal forms. Again, these can be useful to later developers who need to Joining tables that were separated
make changes to your original design. One change that is often made on high-volume in the normalization process to
transaction databases is to apply a process called “denormalization.” In denormaliza- improve performance.
tion, some entities that were separated in the normalization process are rejoined. This
is done for processing and query speed. It is not a process that should be done lightly.
Every act of denormalization reopens the possibility of the various anomalies. But
sometimes the sheer size and volume of transactions on a database make it necessary to
denormalize if the users are not to experience delays.
A database should always be fully normalized first and denormalized only as necessary
for performance. Both the fully normalized design and the changes made for
denormalization should be fully documented.
Things We Have Done
In this chapter, we have
• looked at three types of database anomalies: insert, update,
and delete
• introduced normal forms
Vocabulary
Match the vocabulary word with its definition
• reviewed database designs for First Normal Form
• reviewed database designs for Second Normal Form
• reviewed database designs for Third Normal Form
• reviewed database designs for completeness
1. Normal forms
2. Update anomalies
3. Deletion anomalies
4. First Normal Form
5. Denormalization
6. Insertion anomalies
— a. Where deleting some data inadvertently also removes other data
— b. Removes transient dependencies
— c. Where the same data must be updated in several places, creating the possibility of
mismatched or inaccurate data
— d. Attributes that are related to each other, rather than the key. They form subthemes within
the entity
— e. Rules for removing anomalies and redundancies
— f. An attribute that depends on another attribute, not the key, for its meaning
100 Chapter 5 • Normalization and Design Review
7. Second Normal Form
8. Transient dependencies
9. Functional dependencies
10. Third Normal Form
– g. Removes functional dependencies
– h. The inability to insert data because some other unknown data is required
– i. Removes repeating groups and arrays
– j. The process of rejoining tables that were separated during the normalization process to
improve performance
Things to Look Up
1. Look up database anomalies. See if you can find a good
example explaining each kind of anomaly.
2. Look up the definition of functional dependency. Can you
find a good example?
3. Look up the definition of transitive dependency. Can you
find a good example?
Practices
4. Look up one of the normal forms we did not cover. See if
you can explain it to someone in the class.
5. Look up “denormalization,” and why anyone would want
to do it.
Charlie has a large book collection. He was keeping track of it
in a spreadsheet, but it has grown big enough that he wants
to convert it into a real database. Here is a sample from the
spreadsheet:
1.
2.
3.
What are some of the potential problems with this layout if
carried directly to the database?
Which of the columns in the example are multivalued?
Create a table that would show how you would convert the
sample data into First Normal Form. (Hint: Break the information
in the Titlescolumn into separate fields. Books are
Author Author Country Titles
James Taylor England JavaScript Essentials, South Tech
Books, London, 2010, $14; HTML5
Exposed, Webby Books, London,
2012, $15.50
May Norton United States Big Data Big Promise, Data Press,
San Francisco 2012, $25
Jessica Lewis United States Database Development for the
Cloud, Data Press San Francisco,
$20.35; Data Services, Future Tech
Press, New York $12.95
Scenarios
WILD WOOD APARTMENTS
It is almost time to actually begin building the apartment database,
but you must make sure that the design is solid and that it
captures all the data required by Wild Wood Apartments. The
first step is a design review; then you must review the diagram
for completeness.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
rules and needs of Wild Wood Apartments?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
separated by semicolons)
4. Create an entity diagram for the table you made in Practice 3.
5. List all the functional dependencies you find in the sample
data.
6. Identify and list some potential candidate keys for the new
entities.
7. Create an entity diagram that shows the structure of the
data in Second Normal Form.
8. List any transitive dependencies you find.
9. Create an entity diagram that shows the database in Third
Normal Form.
10. Describe the process you went through to achieve the
normal forms.
VINCE’S VINYL
You have told Vince that you can begin building the database
very soon now, maybe even next week. But before you do that,
you need to make sure the design is solid and complete.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
rules and needs of Vince’s Vinyl?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
GRANDFIELD COLLEGE
You have promised to begin building the database within the
next couple of days. But before you do that, you have to review
the design for normalization and completeness.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness.
Do the entities capture all the data needed to meet
the business rules and needs of Grandfield College IT
Department?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
WESTLAKE RESEARCH HOSPITAL
The start of the double-bind test is approaching rapidly. There
is a great deal of pressure on you to begin building the actual
database. Before you can -do That, though, you must perform
a final review to make sure the database is normalized and
complete.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
rules and needs of Westlake?
Chapter 5 0 Normalization and Design Review 101
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
SHOW TIMES: LOCAL SHOWS AND ACTS
You are almost ready to begin building the database, but before
getting down to actually creating the database, you must review
the diagram for normalization and completeness.
To Do
1. Review the diagram you made from the previous chapter
for all the three levels of normalization.
2. Change the diagram to reflect the fully normalized
design.
3. Document in writing why you made the changes you
did, or why you did not need to make changes.
4. Review the normalized diagram for completeness. Do
the entities capture all the data needed to meet the business
Shows and Venues?
5. Documentation: Save the normalized diagram with
notes about changes made during the normalization process
to your database notebook.
SUGGESTIONS FOR SCENARIOS
Normalization is difficult. The trick is to take each normal form
one at a time. Look at each entity one at a time, to see if each
conforms to the First Nonnal Form. Make sure there are no
repeating groups or multivalued attributes. If there are, then
break them out into new entities. Then repeat the process for
the Second Normal Form. Look at each entity, and make sure
that each is about only one thing. Again, if you find an entity
that is about more than one thing, break it into new entities.
Finally, repeat the process for Third Normal Form, looking for
transitive dependencies, attributes that depend on an attribute
that is not the key, for their value.
As with the design process itself, the normalization process
benefits from discussion and multiple inputs. It is crucial to
have others review the results.
Physical Design
Now that she has the logical design completed, Sharon works on the physical design of the database. The
first thing to decide is what database management system to use. After considering several, Sharon decides
on SQL Server Express. She creates a new database with a data file and a log file. She creates the tables in
the new database, selecting the appropriate data type and setting any constraints for each column. She also
sets up the relationships among the tables. Finally, when she has set up all the database objects, she enters
5 or 10 rows of sample data so she can test the database.
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
• Compare database management systems and determine which best suits current needs
ud Implement a physical design of the database based on the logical ERDs
la Choose appropriate data types for columns
VIEnter sample data into tables
CHOOSING THE MANAGEMENT SYSTEM
Sharon finally feels comfortable with her design. Now it is time to begin actually creating the database. The
first question she must resolve is which database management system to use. One of the first criteria is that
it shouldn’t cost the school anything. That still leaves open several options. Oracle Express and DB2 Express
are tempting because she would love to explore them. But the fact that she doesn’t know either also means
a longer learning curve. Additionally, she knows that the IT staff is unfamiliar with them. The same holds
true, though to a lesser extent for MySQL and PostGres SQL. Both are free and actually more powerful than
any of the express editions, but she is less familiar with them. The IT staff has some familiarity with MySQL,
but still Sharon doesn’t think she can afford the learning curve at this time. That leaves Access and SQL
Server Express.
Things You Should Know
Choosing a DBMS
Choosing the appropriate DBMS requires a great deal of analysis. There are several important factors to consider.
• Compatibility with your network and operating systems
• Hardware and software requirements for the DBMS
• Features of the DBMS in relation to your database requirements
• Familiarity and expertise in the DBMS for database developers and IT personnel
102
Chapter 6 Physical Design 123
Customers
2065552123 Lamont NULL 161 South Western Ave NULL NULL 98001
2065553252 Johnston Apt. 304 1215 Terrace Avenue Seattle WA 98001
2065552963 Lewis NULL 520 East Lake Way NULL NULL 98002
2065553213 Anderson Apt 10 222 SouthernStreet NULL NULL 98001
2065552217 Wong NULL 2832 Washington Ave Seattle WA 98002
2065556623 Jimenez Apt 13 B 1200 Norton Way NULL NULL 98003
Employee Table
cmanning Manning Carol 3/12/2012
btaylor Taylor Bob 4/16/2010
skristoph Kristopherson Stephen 6/2/2014
Product Table
262
CustomerOrder Table
1000 10/8/2014 14:15:00 2065552963 cmanning
1001 10/8/2014 14:25:00 2065556623 cmanning
1002 10/8/2014 14:30:00 2065552963 cmanning
1003 10/8/2014 15:15:00 2065552123 skristoph
1004 10/10/2014 11:15:00 2065552217 btaylor
1005 10/10/2014 12:02:00 2065556623 btaylor
Order Detail Table
1 1000 soda 2 7.25
2 1000 brdstks 1 2.50
3 1000 specialM 1 7.35
4 1001 specialL 1 15.00
5 1002 soda 2 7.25
6 1002 basicM 3 20.00
7 1003 basicM 1 7.35
8 1003 top 4 4.00
9 1004 basicL 1 13.50
10 1005 basicM 2 14.70
Scenarios
WILD WOOD APARTMENTS
You have completed the designs for the apartment management
database. You reviewed it and all the business rules with the owners,
and they are eager to proceed. Now you need to take your
design and translate it into an actual database. Once you have
done that, you know that you will need to enter data to test the
database, to make sure it does, in fact, store all the required data.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
124 Chapter 6 0 Physical Design
4. Create a database diagram, and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types
for each column.
VINCE’S VINYL
Vince is eager to get going. Just today, he had a customer come
in and sell him a dozen old albums. One is quite rare and could
be worth a lot of money. Vince doesn’t want to lose track of it.
He is ready to get organized and start entering his transactions
in the database. You review your design with him and promise
that you will begin building the database immediately. But, you
remind him, it is important to test the database before actually
starting to use it for the business.
To Do
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types for
each column.
GRANDFIELD COLLEGE
The management is afraid of a software audit. The chief systems
manager just came from a meeting where he heard that
a school had just been fined $25,000 for illegally installed software.
The current tracking system probably couldn’t hold up to
an audit. It is crucial that this new database be up and running
soon. You assure the management that it will be done as soon as
is possible, but you want to make sure that it really does what it
is supposed to do. If you implement before it is ready, it might
make matters worse rather than better.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types
for each column.
WESTLAKE RESEARCH HOSPITAL
The Drug study is falling into place. Several potential participants
have already been interviewed. It is vital that the database
be in place soon. You assure the management that you are ready
to begin actually making the database objects but that it is essential
you test and evaluate it before they start to commit data to it.
You promise that you will deliver it as soon as possible.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types for
each column.
SHOW TIMES: LOCAL SHOWS AND ACTS
You have completed your design and checked it out. Now it is
time to actually create the database. Once you have built it, you
can add sample data to it so you can test whether it meets all the
requirements and fulfills all the business rules.
To Do
1. Review your diagram for the database, making sure that
the design is complete and normalized.
2. Create the database in SQL Server.
3. Create the tables in the new database, selecting appropriate
data types for the columns, setting a primary key for
each table, and setting allow nulls as appropriate.
4. Create a database diagram and create the relationships
among tables.
5. Add some sample data to each table.
6. Documentation: Make a data dictionary that lists each
table, all the columns for that table, and the data types
for each column.
SUGGESTIONS FOR SCENARIOS
Make sure your primary keys and foreign keys have the same
data type and same precision (length). Follow your diagram
and make one table at a time. In the database diagram, always
drag the relationship from the primary key to the foreign key.
Before confirming the relationship, always make sure that the
table and column names are correct in the dialog box.
If you need to adjust a table in the database diagram, you
can right click on it and under View, select Normal. That will
display the column names, data type, and whether it will accept
nulls. You can edit the table in this view.
When entering data, you must enter data in the primary key
tables before you can enter into the child or foreign key tables.
The foreign key must match the primary key exactly.
Now that Sharon has built the database and entered some data, she sets out to test the design and make
sure she can satisfy the business requirements. To do this, she is going to use SQL and SQLExpress’s query
analyzer.
CHAPTER OUTCOMES
By the end of this chapter, you will be able to:
ElName the main events in the development of SQL
Run SELECT queries with a variety of criteria
Use the Aggregate functions COUNT, AVG, SUM, MIN, and MAX
1/Use date, time, and other built-in functions
o Join two or more tables in a query
i3INSERT, UPDATE and DELETE records
o Use SQL to test business rules
RUNNING QUERIES
It has been a long day. Sharon had two classes of her own today and then she tutored three students in beginning
database. But she feels some pressure to finish with the tutoring database. Before she can give it to Terry,
she needs to test it to make sure it can do all the things that are required of it. She has entered the sample data;
now she is going to run some sample queries. SQL
She takes out her laptop and sits at the kitchen table. She starts the SQL The programming language used Server Management Studio and opens up the databases in the Object window. to manipulate data and data objects
She clicks the NEW QUERY button on the toolbar to get a New Query Window. in a relational database.
Tu t °
File Edit View Project Debug Tools Window Help
Li I New Quey
FIGURE 7-1 New Query
To get started, in the query window she types
SELECT * FROM Tut
125
126 Chapter 7 SQL
She clicks Tutor in the list and then clicks the Execute button and gets these results:
TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHire Date TutorStatus
980010000 Roberts Martha 2065551467 mroberts@yahoo.com 2012-01-06 Active
980010001 Brown Susan 2065553528 Sb4@hotrnaiLcom 2013-02-01 Active
980010002 Foster Daniel 2065553490 Foster32@aol.com 2014-02-12 Active
980010003 Anderson Nathan 3065556320 NULL 2014-03-02 Inactive
980010004 Lewis Ginger 2065552985 ginger@hotmail.com 2014-03-15 Active
FIGURE 7-2 Result Set
Things-You Should Know
SQL is the programming language used for manipulating database objects and data in relational databases.
It is both an ANSI (American National Standards Institute) and an ISO (International Standards
Organization) standard.
The first version of SQL was developed at IBM in the 1970s to work with their RBase relational
database. The first ANSI standard for SQL was issued in 1986. The ISO committee ratified the standard
in 1987. This first standard was not widely used. Database technologies had already moved past
it. Most database manufacturers had already added features that were not included in the standard.
A major revision was issued in 1992. This standard was much more robust and is still the de facto
-standard-of-many RDBMSs-today; More changes were added to-the standard-in-1999 to define-the – –
use of triggers and procedures. Revisions in 2003 and 2006 defined how to incorporate XML and
XQuery into SQL.
Most RDBMSs comply with the standard to a fairly high degree. What this means for the user
is that the SQL they write for one product will translate fairly easily to another product. Much of
the SQL you write for SQL Server, for instance, will work without change in Oracle or MySQL. Each
RDBMS, however, is free to add proprietary features on to SQL as well as to implement the standard.
Typically, these features are additional functions or administrative extensions.
The Nature of SQL
SQL is a declarative language. This means it is different from the procedural languages you may have
encountered in other programming languages such as C++ or Java or C# or Visual Basic. In those
languages you have to specify how something is to be done. You have to carefully list each step in
the proper order to accomplish a task. In SQL, you say what you want done, not how to do it. In the
preceding example, for instance, Sharon writes
DECLARATIVE LANGUAGE SELECT * FROM Tutor
A language in which programmers
declare what they want to do not
how they want to do it.
The SELECT tells the DBMS you want to retrieve data. The * is a wildcard that says “select all
columns.” The FROM keyword directs the RDBMS to a table in the current database. The statement
as a whole declares “return all the columns and all the rows from the table Tutor.” Again, it declares
what you want to do, not how to do it. The RDBMS determines how to process the request. Different
RDBMSs will process it differently because they have developed different query optimization engines
in order to produce the results as efficiently and quickly as possible.
PROCEDURAL LANGUAGE
A language in which a programmer
defines how to do a given procedure.
THINGS TO THINK ABOUT
What are the advantages of a declarative Ian- What advantages might a procedural language
guage as opposed to a procedural language? have over a declarative language?
SQL is not case sensitive, though the column names and values can be if the database options
are set to be case sensitive. It is traditional, however, to type SQL keywords in all uppercase for readability.
SQL also ignores most white spaces. That means you can organize an SQL statement on the
page any way that makes it most readable to you. In many DBMSs, SQL statements are terminated
by a semicolon. SQL Server does not require the semicolon, though using one can be a good habit to
develop. In this book, the semicolons are not included.
Chapter 7 • SQL 155
Vocabulary
Match the term with the definition.
1. Aggregate Function
2. Alias
3. Cross Join
4. DDL
5. Declarative Language
6. DML
7. Equi Joins – g.
8. Procedural Language
9. Qualified Name
10. Scalar Function
11. SQL
12. Transact SQL
Practices
Data Manipulation Language
A function that operates on a single row at a time
A substitute name for a column or table
Programming language that defines how to accomplish a task
A join that uses the WHERE clause and the equal sign to specify relationships
The language of RDBMS
Data Development Language
A function that operates on multiple rows at a time
A database name that shows a hierarchy of ownership with dot notation
Microsoft SQL Server’s brand of SQL
A programming language in which a programmer defines what to do, not how to do
A join in which each row of the first table is joined with every row in a second table
Use the Pizza database created in the last chapter’s practices
and write SQL to answer these questions:
1. List all last names, phone numbers, and zip of the customers.
2. List only-those from-Zip-code98002.
3. List all the customers that have no first address entered in
the database.
4. List all the products that are priced higher than $10.
5. List all the products priced between $5 and $7.
6. List all the customers whose last name starts with L.
7. What is the average price of a product?
Scenarios
WILD WOOD APARTMENTS
Now that the basic database is in place, the Wild Wood
Apartments managers are eager to see the database in action
and see if it meets all their needs and requirements. It is time to
look at the business rules and test them with some SQL. Look
at the business rules you developed previously, and design
some SQL queries to test them. Documentation: Set up a test
plan. List the rule, the SQL you wrote, and the results. Also
note whether the database passes or fails the test. Your queries
should include the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATEs and/or a DELETE.
VINCE’S VINYL
It is time to test Vince’s database to see if it truly meets his
needs. It is time to look back at the business rules and test
them with some SQL. Look at the business rules you developed
previously, and design some SQL queries to test them.
Documentation: Set up a test plan. List the rule, the SQL you
wrote, and the results. Also note whether the database passes
or fails the test. Your queries should include the following:
8. What is the highest price of a product?
9. What is the total due for order 1003?
10. join the product and the OrderDetail table so that the result
contains the product name, product unit size, and product
urut price as well the charged price. Do it for order 1000.
List all the order and order details for each order made by
the customer with the phone number 2065556623.
Change the price of breadsticks to 3.00.
Process a pizza order for a new customer (this will involve 3
INSERT statements).
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATES and/or a DELETE.
GRANDFIELD COLLEGE
The college is feeling pressurized to get the new system in place.
There could be an inspection of their IT services any time now,
and they want to be ready. It is time to look at the business rules
and test them with some SQL. Documentation: Set up a test
plan. Look at the business rules you developed previously, and
design some SQL queries to test them. List the rule, the SQL you
wrote, and the results. Also note whether the database passes or
fails the test. Your queries should include the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATES and/or a DELETE.
WESTLAKE RESEARCH HOSPITAL
The research program is almost ready to begin. Westlake is in
the process of interviewing potential patients and doctors. It is
11.
12.
13.
156 Chapter 7 • SQL
important that the database be ready soon. It is also important
that it does what it is supposed to do. It is time to look at the business
rules and test them with some SQL. Look at the business
rules you developed previously, and design some SQL queries
to test them. Documentation: Set up a test plan. List the rule, the
SQL you wrote, and the results. Also note whether the database
passes or fails the test. Your queries should include the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATES and/or a DELETE.
SHOW TIMES: LOCAL SHOWS AND ACTS
Everyone is eager to see this database up and running. Look
at the requirements and business rules you established in the
design process. You will use SQL to test those rules and make
sure the database functions the way it needs.
Documentation: Set up a test plan. Look at the business rules
you compiled in Chapter 3 and design some SQL queries to test
them. List the rule, the SQL, and the result. Also note whether
the database passes or fails the test. Your queries should include
the following:
• Two or three simple SELECTS with various WHERE criteria.
• Two or three queries using aggregate functions.
• At least two queries that use joins.
• Two or three INSERT statements.
• One or two UPDATEs and/or a DELETE.
SUGGESTION FOR SCENARIOS
Review your business rules. Many are probably simple to test,
requiring only SELECT statements. Others may be harder. Try
the simple ones first.
You may also find that you need to adjust your sample data.
It may be necessary to insert some data that show a violation of
a rule, or you may need to insert data in order to compare different
dates or times.
Most SQL mistakes are syntax errors. Missing commas or
extra commas are common suspects. The error messages in
the query analyzer do not always pinpoint the exact error. If
you double click the error message, it will place your cursor in
the vicinity of the error. Look all around the region. A missing
comma or a misspelled word may be causing an error later in
the code.
Another common error with joins is the ambiguous column.
This usually involves a key column that occurs in other
tables as a foreign key. Since it occurs in more than one table,
SQL Server cannot determine which table it is from. These columns
should always be qualified with the all name or table
alias.
Chapter 8 • Is It Secure? 175
Vocabulary
Match the definitions to the vocabulary words:
1. Authentication
2. Authorization
3. Disaster recovery plan
4. Big Data
5. Permission
6. Policies
7. Procedures
8. Roles
9. Schema
10. Stored procedures
11. Views
Practices
a. an action that a user has been granted the right to do in a database
b. A stored query or filter that reflects a user’s view of the data
c. The process of confirming a user is who he or she claims to be
d. A set of related permissions
e. A rule for how to do some activity
f. One or more SQL statements grouped to be executed together
g. Enormous structured and unstructured datasets
h. The processes of assigning permissions to authenticated users
i. A plan to recover data and maintain availability after any kind of disaster
j. Step-by-step plan for accomplishing a task
1. Review the pizza database we built in Chapter 6 and queried
in Chapter 7. Identify the users of the database, and determine
what kind of access to the tables each of them needs.
2. Develop a threat analysis for the pizza company database.
3. Create roles for the various types of users in the pizza
database.
4. Create an SQL Server login for a user and assign the user to
a role.
5. Create a view for one of the roles and grant permission to
select from the view to one or more users.
6. Assume you are working for a small bookstore. They have a
database that keeps track of all their inventory and all their
sales and trades with customers. This bookstore also maintains
an online presence with a Web site, where users can
browse the catalog and purchase books using second-party
software to process the payment. There is only one store and
they are located in the downtown area of a city known for
occasional severe earthquakes. Create a disaster recovery
plan for this company.
For Practice 7-11, look at the following stored procedure
and answer the questions that follow.
CREATE PROCEDURE usp_AddRequest
@CourseKey NCHAR(10),
@StudentKey NCHAR(10),
@RequestKey NCHAR(10)
AS
DECLARE @Date DATE
DECLARE @Status NCHAR(10)
SET @Date=GETDATE()
SET @Status = ‘Active’
BEGIN TRAN
BEGIN TRY
INSERT INTO REQUEST(
RequestKey,
CourseKey,
RequestDate,
RequestStatus,
StudentKey)
Values(
@RequestKey,
@CourseKey,
@Date,
@Status,
@StudentKey)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
7. What are the names of parameters in the procedure?
8. What are the names of the variables?
9. What happens to the transaction if there is no error?
10. What happens to the transaction if there is an error?
11. Why do you need a TRY CATCH with a Transaction?
Scenarios
Imo WOOD APARTMENTS
The apartment managers at Wild Wood like what you have
done so far, but as the database takes shape, they have begun
to worry about security. The tenant information should not be
accessible to just anyone. And they would like to keep the financial
information internal, and not let outsiders or other companies
see the details of their operation.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
176 Chapter 8 0 Is It Secure?
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (making a
rent payment, for instance, or a maintenance request).
VINCE’S VINYL
Having shown Vince your work so far, you broach the topic
of security. At first, Vince doesn’t see much need for security
measures, but you point out a few areas that should be considered.
For one thing, Vince probably doesn’t want to share
his list of interested customers. That is valuable information
in itself, and his customers will have an expectation of privacy.
Additionally, the day-to-day financial information concerning
sales and purchases is probably best not available for
general public perusal. You also point out that it is important
that Vince be able to trust his data. He needs to know that no
one has accidentally, or on purpose, messed up his inventory
or sales data.
To do
1. Create tables of the data access needs of Vince’s users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (purchasing
an album, for instance, or recording a customer request).
GRANDFIELD COLLEGE
As with any database, data integrity is important to the software
database at Grandfield College. If they are audited, they
have to show that they know what software they have, how it
is licensed, and on what machines it is installed. Accident and
error are the most likely threats to their data integrity, but it is
always possible that someone might try to purposely disrupt
their data.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (installing a
piece of software, for instance, or processing a software
request).
WESTLAKE RESEARCH HOSPITAL
Security has always been a part of the WestLake Hospital’s
database. In a double-blind study, it is absolutely essential that
no one tampers with the data. Also, patient confidentiality
and the sensitive nature of the study require that the patients’
records and the records of their sessions with the doctors be
kept absolutely private and secure. The researchers are anxious
to see your plan for securing the data.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (making an
appointment, for instance, or letting patients see some of
their doctors’ session notes on them.)
SHOW TIMES: LOCAL SHOWS AND ACTS
Now that you have the database built and ready to go, it is time
to consider the security needs of the databases. Data Integrity is
important. The database won’t be of much use if it’s not trustworthy.
Also, you want to ensure that no one goes in and just
changes things as a joke or with intention of interfering with a
competing venue or band. You also need to think about where
the database is going to be housed and how you are going to
protect it from various potential disasters.
To do
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and
authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and
other tools.
3. Documentation: Document and define all the aspects of
your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of
one of your uses.
7. For extra-credit, create a stored procedure that executes
one of the basic activities for your database (adding a
new show, for instance, or registering a new fan with
their genre and act preferences.)